Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.


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

accept rate: 0%

edited 19 Mar '13, 18:54

Mark%20Culp's gravatar image

Mark Culp

FWIW, if you have a date and a time value, you can simply add them, such as:

   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;

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
accept rate: 34%

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 19 Mar '13, 17:52

question was seen: 3,083 times

last updated: 19 Mar '13, 18:54