I need a query that finds event is occurring between today at 1:00 am and tomorrow at 12:59 am. I have 2 columns.event_date datatype =date and event_time datatype= time
SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(day, 0, GETDATE()), 102))
gives me --2013-03-19 00:00:00.000 but instead of 0.00 I need my time which is 1:00 AM and 12:59 AM of next day.
FWIW, if you have a date and a time value, you can simply add them, such as:
begin declare dt date; declare tm time; set dt = current date; set tm = current time; -- add both (and compare with current timestamp) select dt + tm, current timestamp; end;
The same is true for time literals like '01:00' when you cast them as time values.
So you might select something like:
where event_date + event_type between current date + cast('01:00' as time) and dateadd(dd, 1, current date) + cast('12:59:59' as time)
Note: I don't claim this works efficiently - for that a datetime/timestamp column on the event date/time would surely be helpful...