between today at 1:00 am and tomorrow at 12:59 am

[Former Member]

Hello,

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

This query

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.

[VolkerBarth]

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...