The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 19 Mar '13, 17:52

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 19 Mar '13, 18:54

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264


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

permanent link

answered 19 Mar '13, 18:39

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 19 Mar '13, 18:48

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×21
×4

question asked: 19 Mar '13, 17:52

question was seen: 812 times

last updated: 19 Mar '13, 18:54