The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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


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.3k287438645
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: 801 times

last updated: 19 Mar '13, 18:54