CODEWITHSUNDEEP
sqlsql-server
Koshur
Koshur

Reputation: 478

How to fetch records for certain days based on a timestamp using a sql query?

I want to fetch records from every Saturday. I have a column create_date (timestamp) - 2016-06-22 07:20:22.220

The below query works for me for a single day.

 select * from table
  where create_date > '2016-06-04'
  AND creat_date < '2016-06-05'

But what if I want to fetch records for multiple days say - 2016-06-11, 2016-06-18, 2016-06-25

Upvotes: 0

Views: 1921

Answers (3)

Hemal
Hemal

Reputation: 3760

You can use DATENAME SQL function for this

SELECT *
FROM yourTable
WHERE DATENAME(weekday, create_date) = 'Saturday'

Upvotes: 0

Nolan Shang
Nolan Shang

Reputation: 2328

you can try to use datediff per a refer date. this sample is '2016-06-11'.

select * from table
 where datediff(d,'2016-06-11',create_date) in (0,7,14)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You can use the DATENAME() function for that:

SELECT *
FROM yourTable
WHERE DATENAME(dw, create_date) = 'Saturday'

If you want to also restrict the range of create_date you can add a check in the WHERE clause for that:

SELECT *
FROM yourTable
WHERE DATENAME(dw, create_date) = 'Saturday' AND
      create_date BETWEEN '2016-06-04' AND '2016-06-25'

Upvotes: 3

Related Questions