Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

event is occurring between today at 1:00am and tomorrow at 12:59am Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 3:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 1,118, Visits: 867
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.
Post #1432927
Posted Tuesday, March 19, 2013 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 20,685, Visits: 32,289
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432931
Posted Tuesday, March 19, 2013 5:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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.

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.
Post #1432944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse