asked on

Sql date compare query

Below is some sample table data from tblWagers

wager StartDateTime (stored as datetime)

400 2013-09-06 13:29:35.000

200 2013-09-06 07:29:35.000

1520 2013-09-07 00:59:35.000

500 2013-09-07 01:01:35.000

I am trying to write a query that will return all records with a startdatetime on or after 8am on the current date up to and including any records with a startdatetime of 1 am or earlier the following day

(For the purposes of this example, let's assume current date is 09/06/2013. Given that, the query is looking for 8am on 9/16/2013 to 1am on 9/17/2013)

So in the example above I would expect the query to return

400 2013-09-06 13:29:35.000

1520 2013-09-07 00:59:35.000

Not sure how to format where clause for correct date compare

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

These results:

WAGER	STARTDATETIME
400	September, 06 2013 13:29:35+0000
1520	September, 07 2013 00:59:35+0000

Open in new window

Produced by:

-- truncate time on getdate()
-- dateadd(day, datediff(day,0, <<data-here>> ), 0)

declare @s datetime, @f datetime
set @s = dateadd(hour, 8, dateadd(day, datediff(day,0, getdate() ), 0) )
set @f = dateadd(hour, 17, @s)

select
*
from yourtable
where ( StartDateTime >= @s and StartDateTime < @f )
;

select
*
from yourtable
where ( StartDateTime >= dateadd(hour, 8, dateadd(day, datediff(day,0, getdate() ), 0) )
    and StartDateTime < dateadd(hour, 25, dateadd(day, datediff(day,0, getdate() ), 0) )
      )
;

Open in new window

Question:
is <date> 01:00:00 included in the results?
if it is then
change <
to <=
in the queries above

http://sqlfiddle.com/#!3/e8002/3

Jim Horn

Thanks for the grade. Good luck with your project. -Jim