user2008251 user2008251 - 4 months ago 11
SQL Question

Finding Start and end Date for a week given the week of a year in Teradata (SQL)

I am doing data conversion and i need to get the start and end dates of a given week.

My source has the date stored in a single

int
column
'year_wk'
.

Examples for this column would be

201201 - meaning the first week in 2012
201005 - meaning the fifth week in 2010

I would like to get start and end dates for a given week in a standard
mm/dd/yyyy
format.

So 201201 would give me something like 1/1/2012 and 1/7/2012.

Answer

It won't be pretty and may not be terribly efficient but you can leverage the view SYS_CALENDAR.CALENDAR. I purposely use SELECT * in the example so you can see all of the attributes available to you with the SYS_CALENDAR.CALENDAR view that is a default database on all Teradata installations:

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