CODEWITHSUNDEEP
sqlteradata
user2008251
user2008251

Reputation: 23

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.

Upvotes: 2

Views: 14833

Answers (5)

user9932381
user9932381

Reputation: 31

SELECT TD_WEEK_END (date) - 6

the TD_WEEK_END (date) is the end of the weekend. Simply subtract 6 from date to have the beginning of the week.

NOTE: This answer set will always return Sunday since Sunday is the first day of the week.

Upvotes: 2

dev
dev

Reputation: 961

Trying to improve my SQL, This would be a simple soultion:

 SELECT CALENDAR_DATE,DAY_OF_WEEK
 FROM SYS_CALENDAR.CALENDAR TERADATA
 WHERE SUBSTR(CAST((CAST(CALENDAR_DATE AS DATE FORMAT 'MMDDYYYY')) AS VARCHAR(8) ),5,4) = '2010'
AND WEEK_OF_YEAR='05' 
AND DAY_OF_WEEK IN (1,7)
ORDER BY CALENDAR_DATE

Upvotes: 0

Sparky
Sparky

Reputation: 15085

Here is some SQL code that will build a table of starting and ending dates for a given year..

If you build this table for all years in question, the rest should be accomplished via a JOIN statement

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

Upvotes: 1

Art
Art

Reputation: 5782

I'm not familiar with Teradata but here's the Oracle ISO week query with week start and end dates for the whole year of 2013. This should work in any SQL. For non-iso week change week formats to WW - forgot to rename mydate to start_date in query:

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

Week numbers calendar: http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php

Upvotes: 2

Rob Paller
Rob Paller

Reputation: 7786

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

Upvotes: 3

Related Questions