All Forums Database
san28 20 posts Joined 01/14
15 Jan 2014
Getting week of the year from a date

Hello everyone,
 I need to get the week of the year from a date, but 1st of Jan being the 1st week. (no matter what day it falls on). How can I get this in Teradata sql? For eg. 6 Jan 2013 is week 2.  Can someone please help?
Thank you so much.
San

 

M.Saeed Khurram 544 posts Joined 09/12
15 Jan 2014
SELECT TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE);

 

Khurram

dnoeth 4628 posts Joined 11/04
15 Jan 2014

You should change the calculation to the ISO calendar:

TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE, 'ISO')

And you probably need the ISO-year, too:

YEARNUMBER_OF_CALENDAR(current_date, 'ISO')

In TD14 there's also Oracle's TO_CHAR:

TO_CHAR(d, 'iyyyiw')

Dieter

san28 20 posts Joined 01/14
22 Jan 2014

Thanks Khurram and Dieter - But I am getting an error while running

 

SELECT TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE);

It says - Cannot resolve column - TD_SYSFNLIB. Specify Table or View.
 

M.Saeed Khurram 544 posts Joined 09/12
22 Jan 2014

You can find the complete info about this function at this link, and can use the appropriate syntax.
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/SQL_Reference/B035_1145_111A/Business_Calendars.079.057.html
 

Khurram

dnoeth 4628 posts Joined 11/04
22 Jan 2014

What's you Teradata release? <13.10?

Dieter

san28 20 posts Joined 01/14
22 Jan 2014

Its teradata 13.0

VandeBergB 182 posts Joined 09/06
22 Jan 2014
Select mv.col1, mv.col2, c.week_of_year from MyView mv inner join sys_calendar.calendar c on mv.my_dat= c.calendar_date

Join to the sys_calendar.calendar view on your_date = calendar_date and pull the week_of_year column from the system calendar.
 

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
22 Jan 2014

Hi San,
when you want 1st of january to be in week 1, both sys_calendar and iso calendar will fail.
Can you elaborate about the rules for this calendar?
When does a week start, sunday or monday?
What about the last days of a year, does 2013-12-31 belong to week 1 of 2014 or week 52 of 2013?
Dieter

Dieter

san28 20 posts Joined 01/14
23 Jan 2014

Thank you all  for your help with this. So Jan 1 2014, will always belong to week 1 and then subsequent weeks will follow. Week starts on a Sunday. Does it make sense?

dnoeth 4628 posts Joined 11/04
24 Jan 2014

This is quite similar to the week_of_year calculation in sys_calendar (as long as week 1 of 2014 doesn't include 2013-12-31).
Using sys_calendar.calendar:

((day_of_year - day_of_week) +13) / 7

Standalone:

((cdate - ((EXTRACT(YEAR FROM cdate) - 1900) * 10000 + 0101 (DATE))) - ((cdate - DATE '0001-01-07') MOD 7)  + 13) / 7

 

Dieter

You must sign in to leave a comment.