Reg: how to extract a week number from date column

General
Enthusiast

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.

4 REPLIES
Senior Apprentice

Re: Reg: how to extract a week number from date column

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

Enthusiast

Re: Reg: how to extract a week number from date column

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

Enthusiast

Re: Reg: how to extract a week number from date column

how do i need to join my column with system calender

Senior Apprentice

Re: Reg: how to extract a week number from date column

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