check if date time is of today after 8 am server

Question

I have the following table

ID      UpdatedDate
---     ------------
1   2013-03-04 08:05:07.203
2   2013-03-04 07:05:07.203
3   2013-03-05 10:05:07.203

Now I want to show records only which occur after 8.oo AM today only.

for that I am doing as following

select * from tab
where 
LastUpdatedDate > GETDATE()
and datepart(hh, LastUpdatedDate) >= 8

the issue occurs if I run this query after the time mentioned in the updatedDate. in that case LastUpdatedDate > GETDATE() fails and returns nothing.

Any idea on how to go about this issue?

possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Matt Johnson-Pint, Mar 05 '13 at 04:21
Yes I m using Sql server and I don't need just date part.. i also need to check on time part. — Gautam, Mar 05 '13 at 04:22
Is LastUpdatedDate actually UpdatedDate in your table or am I missing something? Is that a typo? — Dan Metheus, Mar 05 '13 at 04:55

score 2 · Accepted Answer · answered Mar 05 '13 at 04:22

2

select * from tab
where 
    convert(varchar(11),LastUpdatedDate,101) > convert(varchar(11),getdate(),101)
and 
    convert(varchar(11),LastUpdatedDate,108) >= '08:00:000'

101 - extract date part
108 - extract time part ( 24 hour clock format)

answered Mar 05 '13 at 04:22

Mudassir Hasan

28,083
20
99
133

score 2 · Answer 2 · answered Mar 05 '13 at 07:35

I know you've already accepted an answer, but doing this using string comparisons is a really bad idea - it precludes any possibility of using an index.

Instead, why not round the return value from GETDATE() to 08:00 today, and directly compare the LastUpdatedDate column to that:

select * from tab
where 
    LastUpdatedDate >=
       DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T08:00:00')

The DATEADD/DATEDIFF pair are used to do the rounding I've described. They work because of the fixed relationship between the two dates I'm using.

score 1 · Answer 3 · answered Mar 05 '13 at 06:16

dateadd(day, datediff(day, 0, getdate()), 0) will give you the date of today with time part 00:00:00. Useut that the third parameter in dateadd(hour, 8, ... and you have a datetime value where the time is 08:00:00 that you can use to compare against your column.

Applying functions to columns should if possible always be avoided because that will make your query Non-Sargable.

select *
from tab
where UpdatedDate >= dateadd(hour, 8, dateadd(day, datediff(day, 0, getdate()), 0))

check if date time is of today after 8 am server

3 Answers3