SQL 返回 2008 年第 13 周的结果（未分组）

发布于 2024-07-25 12:05:53 字数 373 浏览 4 评论 0原文

我试图在 oracle SQL 中使用“年-周”格式来仅返回“年-周”范围内的结果。

这是我正在尝试的，

SELECT * FROM widsys.train trn WHERE trn.WID_DATE>=TO_DATE('2008-13', 'YYYY-IW') AND trn.WID_DATE<=TO_DATE('2008-15', 'YYYY-IW') ORDER BY trn.wid_date

但它会出现此错误。

ORA-01820: 格式代码无法出现在日期输入格式中，但在 ORA 上失败

对于我可以使用的内容有什么建议吗？

非常感谢，

托马斯

原文

I'm trying to use a Year-Week format in oracle SQL to return results only from a range of Year-Weeks.

Here's what I'm trying

SELECT * FROM widsys.train trn WHERE trn.WID_DATE>=TO_DATE('2008-13', 'YYYY-IW') AND trn.WID_DATE<=TO_DATE('2008-15', 'YYYY-IW') ORDER BY trn.wid_date

but it shoots this error.

ORA-01820: format code cannot appear in date input format but fails on ORA

Any suggestions on what I can use?

Thanks kindly,

Thomas

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧伤慢歌 2024-08-01 12:05:54

您可以翻转它并进行字符串比较。

 SELECT * 
 FROM widsys.train trn 
 WHERE to_char(trn.WID_DATE, 'YYYY-IW') ='2008-13'
 ORDER BY trn.wid_date;

我认为 to_date() 不适用于 IW 是有道理的，因为一周的开始有点不明确 - 有些语言环境在周日开始一周，其他语言周一等。生成一年中被截断的一周，与截断的一周不同日、月或年，因此会很困难。

编辑：

我同意自然排序就足够了，但你让我开始思考。如何比较给定日期和格式化的 YYYY-IW 字符串？我尝试了一下。这种尝试可以被设计成一个接受日期和 YYYY-IW 格式的 varchar 的函数，但是您需要替换硬编码字符串和 to_date() 函数调用，并执行一些清理。
如果传入的日期在年/年周之前，则返回 -1；如果日期在指定的年/年周内，则返回 0；如果在年/周之后，则返回 1。它适用于一年中的 ISO 周，“IW”格式标记也是如此。

 select (case 
      when input.day < a.startofweek then -1
      when input.day < a.startofweek+7 then 0
      else 1 end)
 from 
 (select 
 -- //first get the iso offset for jan 1, this could be removed if you didn't want iso 
    (select (max(to_number(to_char(to_date('2008','YYYY') + level,'DDD')))) 
     from dual 
     where to_number(to_char(to_date('2008','YYYY')  + level,'IW')) 
      <2 connect by level <= 6) -6
    +
 -- //next get the days in the year to the month in question   
    (select ((to_number(substr('2008-13', 6,2))-1)*7) from dual) startofweek 
     from dual) a, 
 -- //this is generating a test date
  (select to_number(to_char(to_date('2008-07-19', 'YYYYMMDD'), 'DDD')) day 
    from dual) input, 
  dual

You could flip it around and do a string compare.

 SELECT * 
 FROM widsys.train trn 
 WHERE to_char(trn.WID_DATE, 'YYYY-IW') ='2008-13'
 ORDER BY trn.wid_date;

I suppose it makes sense that to_date() doesn't work with IW, as the start of the week is somewhat ambiguous - some locales start the week on Sunday, others Monday, etc. Generating a truncated week of the year, unlike a truncated day, month, or year, would therefore be difficult.

edit:

I agree that the natural sort should suffice, but you got me thinking. How would you compare a given date and a formatted YYYY-IW string? I took a stab at it. This attempt could be fashioned into a function that takes a date and a YYYY-IW formatted varchar, but you would need to replace the hard coded strings and the to_date() function calls, and perform some clean up.
It returns a -1 if the passed in date is before the year/weekofyear, 0 if the date falls within the specified weekofyear, and 1 if it is after. It works on ISO week of year, as does the 'IW' format token.

 select (case 
      when input.day < a.startofweek then -1
      when input.day < a.startofweek+7 then 0
      else 1 end)
 from 
 (select 
 -- //first get the iso offset for jan 1, this could be removed if you didn't want iso 
    (select (max(to_number(to_char(to_date('2008','YYYY') + level,'DDD')))) 
     from dual 
     where to_number(to_char(to_date('2008','YYYY')  + level,'IW')) 
      <2 connect by level <= 6) -6
    +
 -- //next get the days in the year to the month in question   
    (select ((to_number(substr('2008-13', 6,2))-1)*7) from dual) startofweek 
     from dual) a, 
 -- //this is generating a test date
  (select to_number(to_char(to_date('2008-07-19', 'YYYYMMDD'), 'DDD')) day 
    from dual) input, 
  dual

回复收藏 0 原文

云醉月微眠 2024-08-01 12:05:54

如何

select * from widsys.train trn 
  where to_char(trn.wid_date, 'YYYY-IW') =  ?
  order by trn.wid_date

范围和范围

select * from widsys.train trn 
  where to_char(trn.wid_date, 'YYYY-IW') between ? and ?
  order by trn.wid_date

范围将使用字符串比较，如果较小的数字用零填充，则效果很好：
“2009-08”而不是“2009-8”。但“IW”格式会进行这种填充。

How about

select * from widsys.train trn 
  where to_char(trn.wid_date, 'YYYY-IW') =  ?
  order by trn.wid_date

and for ranges

select * from widsys.train trn 
  where to_char(trn.wid_date, 'YYYY-IW') between ? and ?
  order by trn.wid_date

The range will use string comparisons, which works fine if smaller numbers are zero-padded:
"2009-08" and not "2009-8". But the 'IW' format does this padding.

回复收藏 0 原文