All Forums General
chaitu_kanna 14 posts Joined 07/13
02 Aug 2013
Reg: how to extract a week number from date column

Hi all ,
I have a table with "Entery_Date" as a date column and I want to group by entries by week number so that I can get how many entries where there in a particular week.

dnoeth 4628 posts Joined 11/04
02 Aug 2013

Define "week_number", ISO or other?
Depending on your TD release you might use an existing UDF or create a SQL-UDF.
Or simply join to a calendar table.
 
Dieter

Dieter

chaitu_kanna 14 posts Joined 07/13
02 Aug 2013

hey Dieter .. can i get any example ...

chaitu_kanna 14 posts Joined 07/13
02 Aug 2013

how do i need to join my column with system calender
 

dnoeth 4628 posts Joined 11/04
02 Aug 2013

What definition of week do you need? Does it start on sunday or monday?
Do you need the actaul week number or just the date of the first day of a week? What rules do you have regarding the starting week number within year?
e.g. this retuns the first day of any week:

datecol - ((datecol - DATE '0001-01-01') MOD 7) -- week starts on monday
datecol - ((datecol - DATE '0001-01-07') MOD 7) -- week starts on sunday

 
Every calendar includes a week, so just join to it and GROUP BY calendar.week_of_calendar
 
Dieter

Dieter

You must sign in to leave a comment.