March 19, 2013 at 3:57 pm
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 gives me,
SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(day, 0, GETDATE()), 102))
--2013-03-19 00:00:00.000 but instead of 0.00 I need my time whihc is 1:00 AM and 12:59 AM of next day.
March 19, 2013 at 4:16 pm
Something like this, just off the top of my head:
declare @StartDate datetime,
@EndDate datetime;
select
@StartDate = dateadd(hh, 1,dateadd(dd, datediff(dd, 0, getdate()), 0)),
@EndDate = dateadd(dd, 1, @StartDate);
select ...
from mytable mt
where mt.someeventdatetime >= @StartDate and mt.someeventdatetime < @EndDate;
March 19, 2013 at 5:37 pm
Just to annoy you Lynn
select ...
from mytable mt
inner join (
select dateadd(hh, 1, dateadd(dd, datediff(dd, 0, getdate()), 0)) StartDate
) DT ON mt.someeventdatetime >= DT.StartDate and mt.someeventdatetime < dateadd(dd, 1, DT.StartDate);
I guess it's your turn now.:hehe:
On a serious note, it could be useful to add a computed column to the table:
DATEADD(dd, DATEDIFF(dd, 0, DATEADD(hh, -1, someeventdatetime )), 0) RegistryDate
or, if it's not an option, create a view like this:
select DATEADD(dd, DATEDIFF(dd, 0, DATEADD(hh, -1, someeventdatetime )), 0) RegistryDate
, *
from mytable mt
It will indicate which day any registered event belongs to.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy