我正在进行数据转换,我需要获取给定周的开始日期和结束日期。
我的源将日期存储在单个int
列'year_wk'
中。
本专栏的示例如下
201201 -表示2012年的第一周201005 -表示2010年的第五周
我想以标准的mm/dd/yyyy
格式获取给定周的开始和结束日期。
所以201201大概是2012年的1/1和1/7。
发布于 2013-01-25 01:51:28
它不会很漂亮,也可能效率不是很高,但您可以利用视图SYS_CALENDAR.CALENDAR
。我特意在示例中使用SELECT *
,这样您就可以使用SYS_CALENDAR.CALENDAR
视图查看所有可用的属性,该视图是所有Teradata安装上的默认数据库:
SELECT *
FROM Sys_Calendar."CALENDAR"
WHERE Year_of_Calendar = CAST(SUBSTRING('201201' FROM 1 FOR 4) AS INTEGER)
AND Week_of_Year = CAST(SUBSTRING('201201' FROM 5 FOR 2) AS INTEGER)
UNION
SELECT *
FROM Sys_Calendar."CALENDAR"
WHERE Year_of_Calendar = CAST(SUBSTRING('201005' FROM 1 FOR 4) AS INTEGER)
AND Week_of_Year = CAST(SUBSTRING('201005' FROM 5 FOR 2) AS INTEGER);
发布于 2013-01-25 02:19:25
我不熟悉Teradata,但这里是Oracle ISO week查询,其中包含2013年全年的周开始和结束日期。这在任何SQL中都应该有效。对于非iso周,将周格式更改为WW -在查询中忘记将mydate重命名为start_date:
SELECT mydate -- 1/1/2013 --
, TRUNC(mydate, 'iw') wk_starts
, TRUNC(mydate, 'iw') + 7 - 1/86400 wk_ends
, TO_NUMBER (TO_CHAR (mydate, 'IW')) ISO_wk#_iw
, TRUNC(mydate, 'w') week_start_date
, TRUNC(mydate, 'w') + 7 - 1/86400 AS week_end_date
FROM
(
SELECT TRUNC(SYSDATE, 'YEAR')-1 + LEVEL AS mydate -- 11/1/2013 --
FROM dual
CONNECT BY LEVEL <=
(-- First day of next year - first day of curr year --
SELECT TRUNC(ADD_MONTHS (SYSDATE, 12), 'Y')-TRUNC(SYSDATE, 'Y') "Num of Days"
FROM dual
)
)
/
SQL>
START_DATE WK_STARTS WK_ENDS ISO_WK#
------------------------------------------------------------------
1/1/2013 12/31/2012 1/6/2013 11:59:59 PM 1
1/2/2013 12/31/2012 1/6/2013 11:59:59 PM 1
...
...
1/7/2013 1/7/2013 1/13/2013 11:59:59 PM 2
1/8/2013 1/7/2013 1/13/2013 11:59:59 PM 2
...
...
1/14/2013 1/14/2013 1/20/2013 11:59:59 PM 3
1/15/2013 1/14/2013 1/20/2013 11:59:59 PM 3
周数日历:http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php
发布于 2013-01-25 01:17:18
下面是一些SQL代码,它们将构建给定年份的开始日期和结束日期的表。
如果您针对所有年份构建此表,则应通过JOIN语句完成其余操作
-- Create weekrange table
Create table Weekrange
(YearNo INT,
WeekNo INT,
StartDay DATETIME,
EndDay DATETIME)
-- Populate first row
insert into WeekRange
select 2013,1, DateAdd(d,-DATEPART(dw,'1/1/2013'),'1/1/2013')+1 as FirstDay,null -- Compute first day
update Weekrange set EndDay = DATEADD(D,6,StartDay) -- Determine last day
-- Add next 52 rows
declare @id INT
set @id=2
while @id <=53
begin
insert into Weekrange (YearNo,WeekNo) values (2013,@id)
set @id=@id+1
update Weekrange
set StartDay = DATEADD(D,1,xx.EndDay),
EndDay = DATEADD(D,7,xx.EndDay)
from
(select * from WeekRange ) xx
where xx.weekno=weekrange.WeekNo-1
end
select * from Weekrange
https://stackoverflow.com/questions/14506391
复制相似问题