raymantle raymantle - 1 month ago 9
SQL Question

Return results base on calendar Week

I have a table with the following rows.

Name Value Links DateSetup
Andrew 10000.00 1 2017-06-06 14:00:44.663
Gerard 35000.00 1 2017-06-07 09:56:18.943
Dominic 70000.00 1 2017-06-07 11:35:56.080
Dominic 25000.00 1 2017-06-08 12:48:02.940
Jim 4800000.00 4 2017-06-08 14:05:00.283
Dominic 600000.00 1 2017-06-08 14:14:15.170
Andrew 350000.00 2 2017-06-09 09:11:19.630
Andrew 75000.00 1 2017-06-09 09:15:58.147
Gerard 115000.00 1 2017-06-09 14:29:18.977
Andrew 200000.00 1 2017-06-09 16:50:32.790
Owen 100000.00 1 2017-06-12 09:33:56.477
Gerard 500000.00 1 2017-06-12 09:49:29.887
Andrew 130000.00 1 2017-06-12 12:03:38.340
Cathal 25500.00 1 2017-06-13 11:11:02.560
Cillian 5000000.00 2 2017-06-13 14:05:21.643
Cillian 8000000.00 2 2017-06-13 14:20:24.777
Cillian 400.00 2 2017-06-13 14:48:51.447


I need to run a query to return all results for calendar week 23.

I currently use the following but want to change from providing the between dates to calendar week.

SELECT Name, Value, Links,DateSetup
FROM setup
WHERE DateSetup BETWEEN '05-06-2017 00:00:00' AND '11-06-2017 00:00:00'


Any help would be grateful.

Answer Source

As there is no DB tagged:

For SQL Server you could use:

SELECT Name
      ,Value
      ,Links
      ,DateSetup
  FROM setup
 WHERE DATEPART(wk, DateSetup) = 23

MySQL should work like:

SELECT Name
      ,Value
      ,Links
      ,DateSetup
  FROM setup
 WHERE WEEK(DateSetup) = 23