Getting week number of date in SQL

sql date week-number database-agnostic
radlan picture radlan · Mar 15, 2013 · Viewed 19.4k times · Source

Is there a way to get the week number of a date with SQL that is database independent?

For example to get the month of a date I use:

SELECT EXTRACT(MONTH FROM :DATE)

But the EXTRACT function doesn't know about weeks in SQL92.

Please pay attention to the fact that I want a solution that is database independent! Do not misinterpret this as a question regarding MS SQL Server.

Answer

user359040 picture user359040 · Mar 15, 2013

There doesn't appear to be a single standard SQL function to extract the week number from a date - see here for a comparison of how different SQL dialects can extract different dateparts from date fields.

Related questions

Get the week start date and week end date from week number

I have a query that counts member's wedding dates in the database. SELECT SUM(NumberOfBrides) AS [Wedding Count] , DATEPART( wk, WeddingDate) AS [Week Number] , DATEPART( year, WeddingDate) AS [Year] FROM MemberWeddingDates GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate) ORDER …

Read More
How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Select data from date range between two dates

I have a table Named Product_Sales and it holds data like this Product_ID | Sold_by | Qty | From_date | To_date -----------+---------+-----+------------+----------- 3 | 12 | 7 | 2013-01-05 | 2013-01-07 6 | 22 | 14 | 2013-01-06 | 2013-01-10 8 | 11 | 9 | 2013-02-05 | 2013-02-11 Now …

Read More