Getting week of the year from a date

Database
Enthusiast

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




Tags (3)
10 REPLIES
Enthusiast

Re: Getting week of the year from a date

SELECT TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE);
Khurram
Junior Contributor

Re: Getting week of the year from a date

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')

Enthusiast

Re: Getting week of the year from a date

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.

Enthusiast

Re: Getting week of the year from a date

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.0...

Khurram
Junior Contributor

Re: Getting week of the year from a date

What's you Teradata release? <13.10?

Enthusiast

Re: Getting week of the year from a date

Its teradata 13.0

Enthusiast

Re: Getting week of the year from a date

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.

Junior Contributor

Re: Getting week of the year from a date

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

Enthusiast

Re: Getting week of the year from a date

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?