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