CODEWITHSUNDEEP
t-sqlgetdate
GPH
GPH

Reputation: 1897

Getdate between times

Sorry if this has been asked somewhere all ready but I've struggled to find the answer! I have a date time field with is a date time stamp on activity.

I need to have a query that only brings back information for data that's between today's date and events between 08:00 and 10:00.My brains says it should be straight forward but i cant suss it.

Thanks

Upvotes: 0

Views: 1989

Answers (3)

Alexey A.
Alexey A.

Reputation: 902

select ...
where dateColumn between 
  convert(varchar(10), getdate(), 120) + 'T08:00:00' 
  and convert(varchar(10), getdate(), 120) + 'T10:00:00'

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

To get a particular point in time, I tend to use the DATEADD/DATEDIFF pattern. It can look a bit unwieldy, but performs quite nicely, and once you're used to seeing it, it becomes more readable:

SELECT * --TODO - Pick columns
FROM Activities
WHERE OccurredAt BETWEEN
     DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T08:00:00') AND
     DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T10:00:00')

Here I'm using it twice. It just relies on the relationship between two date constants that you pick because they exhibit the features that you want. So here:

DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T08:00:00')

I'm calculating the number of (whole) days that have elapsed between 1st January 2001 and right now. I'm then adding that (whole) number of days onto 08:00 on the 1st January 2001 - which will, logically, produce 08:00 today.

Upvotes: 2

IndoKnight
IndoKnight

Reputation: 1864

Here you go...

SELECT * FROM <TABLE_NAME> WHERE EVENTS_DATE BETWEEN
DATEADD(hour, 8, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) 
AND 
DATEADD(hour, 10, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

If you use a variable in TSQL then store today's date in that

DATEADD(day, DATEDIFF(day, 0, GETDATE())

Upvotes: 1

Related Questions