2

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.

tshepang
  • 12,111
  • 21
  • 91
  • 136
radlan
  • 2,393
  • 4
  • 33
  • 53
  • 1
    what about **WEEK('2013-01-15')** OR **SELECT extract(week FROM now())** – Gopesh Sharma Mar 15 '13 at 12:43
  • 1
    Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. – Taryn Mar 15 '13 at 13:01
  • 1
    @bluefeet: he/she is asking for a solution that "*is database independent*". –  Mar 15 '13 at 13:02
  • @a_horse_with_no_name oops, I missed that. – Taryn Mar 15 '13 at 13:03
  • Which programming language are you using? When using JDBC (maybe ODBC as well) you could use `{fn week({d '2031-03-15'})}` (not sure if every driver supports that though) –  Mar 15 '13 at 13:05
  • @GopeshSharma Unfortunately SQL92 doesn't define the `week` keyword for the `EXTRACT` function. Also WEEK() is no standard function. – radlan Mar 15 '13 at 13:16
  • @all As a_horse_with_no_name pointed out, I need a **database independent** solution. I have edited my question to emphasize this fact. – radlan Mar 15 '13 at 13:17
  • @a_horse_with_no_name I am using JDBC, but it is necessary that this can be done in the SELECT statement. The framework I am forced to used is very unflexible. What do you mean where I should put the `{fn week({d '2031-03-15'})}`? – radlan Mar 15 '13 at 13:19
  • You can put that directly into a SQL Statement as long as you run that through a JDBC driver, e.g. `SELECT {fn week({d '2013-03-15'})}`. See here for an example: http://docs.oracle.com/javadb/10.8.2.2/ref/rrefjdbc1020262.html –  Mar 15 '13 at 13:28
  • You should also be aware that the week-numbering is not implemented identically in every DBMS: http://en.wikipedia.org/wiki/Week_number#Week_numbering –  Mar 15 '13 at 13:32
  • @a_horse_with_no_name I didn't know about these JDBC escapes. Although it would be even better to have this in pure SQL, it may be a good choice to achieve this database independence. – radlan Mar 15 '13 at 13:43

6 Answers6

2

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.

0

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

khush
  • 161
  • 1
  • 3
  • 9
0

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

clement
  • 4,204
  • 10
  • 65
  • 133
  • Apart from the fact, that I not fully understand what this is supposed to do, is your example only for MS SQL Server? I need a solution that also works with other databases. – radlan Mar 15 '13 at 13:09
  • You should edit this information into your answer (not just the link, the main informations inside as well) – SztupY Mar 15 '13 at 13:17
  • @clement It seems to me that DATENAME() is a MS SQL Server only function. Also RIGHT() doesn't seem to be very independent. At least Oracle 10g doesn't know it. (BTW: Where the hell do I get the full specs of the SQL standard!? It is a pain to find out which functions are database specific and which not.) – radlan Mar 15 '13 at 13:30
  • if you gonna find sql statment that does that in both sql, pl-sql +oracle, I will be very happy for you ! – clement Mar 15 '13 at 13:31
  • @Radlan: the full ANSI spec is only available if you pay for it. But if you google a bit, there are some PDFs with draft releases available. I have no idea what the legal situation for that is, so I'm not going to post the links –  Mar 15 '13 at 13:35
0

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

Art
  • 5,616
  • 1
  • 20
  • 22
  • Doing it by hand would also be possible. But then I have to find out the last day of the current week in SQL, which would lead to the next problem. ;-) – radlan Mar 15 '13 at 14:06
  • You cannot extract week from month. You can only get the week number from date or manually. And the only non db solution is doing it as in my example because it can be done in any database. The rest is db specific solutions. Extract date, give proper format to date to get the week number. These are your only choices. – Art Mar 15 '13 at 14:09
-1

The best idea I found is 

SELECT ROUND(((DAY(NOW())-1)/7)+1)
Suganthan Madhavan Pillai
  • 5,495
  • 9
  • 51
  • 84
-1
select (DATEPART(yyyy , CAST(GETDATE() AS datetime)) * 100 + 
        DATEPART(ww , CAST(GETDATE() AS datetime))) as week
VMAtm
  • 27,943
  • 17
  • 79
  • 125
Saral
  • 1