Getting week number of date in SQL

问题:

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.

回答1:

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.

回答2:

You can try this.

declare @date datetime select @date='2013-03-15 00:00:00' select 'Week No: '+ convert(varchar(10),datepart(wk,@date)) as weekno

回答3:

Try this one : SELECT DATENAME(yy,GETDATE())+RIGHT(DATENAME(wk,GETDATE()),2)

To understand DATENAME, please follow this link : sqltutorials.blogspot.be/2007/05/sql-datename.html and for the right, suite101.com/article/sql-functions-leftrightsubstrlengthcharindex-a209089 . There are examples to better understand ;-) It will work on MS and other sql servers normally ;-)

回答4:

The only db independent solution I see is to get the number of days between today and Jan 1 of curr. year. Then divide it by 7 and round it up. There is 73 days from Jan 1 till today, which gives 10.43 as week number. The ISO week number is 11.

Correction: the number of days between last day of the current week and Jan 1 of curr. year. In this case the ISO week is 10, and 68/7 = 10 (rounded).

回答5:

The best idea I found is

SELECT ROUND(((DAY(NOW())-1)/7)+1)

回答6:

select (DATEPART(yyyy , CAST(GETDATE() AS datetime)) * 100 + 
        DATEPART(ww , CAST(GETDATE() AS datetime))) as week