|
flamblaster
Constraint Violating Yak Guru
384 Posts |
 Posted - 2011-09-23 : 09:55:38
|
| Yesterday, an op posted a question which included the need to return results based on a date range as well as a time range. The op had a datetime stamp though. Since they're not broken up, it seems to me that you're forced to add dateadd's/datediff's and converts to the where clause.I was trying to see if there's a way around that and the only thing I could come up with was to pull the data from the original table and insert into a new table (table variable in this example), then do the date and time filters on the table variable. Example A shows my basic understanding of the OP's original construct (more or less) and then an alternative that I made with the dates and times split. The thing is, it's looking like it isn't really any more efficient; I'm not sure if it's because of a low number of records, or if it's just still needing to do a full table scan based on the original table.Example A[CODE]--Set Start and End Dates Start and End timesdeclare @StartDate Date ,@EndDate Date ,@StartTime Time ,@EndTime Time set @StartDate='2001-01-02'set @EndDate='2001-02-10'set @StartTime='08:00:00'set @EndTime='17:00:00'--Table to show example of datetime stamp for an attributecreate table #DatesTimes (id int identity, DT datetime, SomeAttribute varchar(20), CONSTRAINT pk_DT Primary Key (ID))insert into #DatesTimes (DT, SomeAttribute)values ('2001-01-01 07:30:00', 'Red Light'),('2001-01-01 16:15:45', 'Red Light'),('2001-01-10 08:01:00', 'Yellow Light'),('2001-02-03 10:00:05', 'Red Light'),('2001-02-04 17:10:05', 'Red Light'),('2001-02-04 18:00:00', 'Red Light'),('2001-02-05 06:00:05', 'Red Light'),('2001-02-07 09:00:05', 'Red Light'),('2001-03-31 12:00:05', 'Red Light'),('2001-04-03 14:00:05', 'Red Light'),('2001-01-01 07:30:00', 'Red Light'),('2001-01-01 16:15:45', 'Red Light')select id, DT, SomeAttribute, DATEADD(dd, 0, DATEDIFF(dd, 0, dt)) as DatePortion, CONVERT(CHAR(12), DT,114) as TimePortionfrom #DatesTimeswhere DATEADD(dd, 0, DATEDIFF(dd, 0, dt))>=@StartDate and DATEADD(dd, 0, DATEDIFF(dd, 0, dt))<=@EndDate and CONVERT(CHAR(12), DT,114)>=@StartTime and CONVERT(CHAR(12), DT,114)<@EndTimedrop table #DatesTimes[/CODE]EXAMPLE B[CODE]--Set Start and End Dates Start and End timesdeclare @StartDate Date ,@EndDate Date ,@StartTime Time ,@EndTime Time set @StartDate='2001-01-02'set @EndDate='2001-02-10'set @StartTime='08:00:00'set @EndTime='17:00:00'--Table to show example of datetime stamp for an attributecreate table #DatesTimes (id int identity, DT datetime, SomeAttribute varchar(20), CONSTRAINT pk_DT Primary Key (ID))insert into #DatesTimes (DT, SomeAttribute)values ('2001-01-01 07:30:00', 'Red Light'),('2001-01-01 16:15:45', 'Red Light'),('2001-01-10 08:01:00', 'Yellow Light'),('2001-02-03 10:00:05', 'Red Light'),('2001-02-04 17:10:05', 'Red Light'),('2001-02-04 18:00:00', 'Red Light'),('2001-02-05 06:00:05', 'Red Light'),('2001-02-07 09:00:05', 'Red Light'),('2001-03-31 12:00:05', 'Red Light'),('2001-04-03 14:00:05', 'Red Light'),('2001-01-01 07:30:00', 'Red Light'),('2001-01-01 16:15:45', 'Red Light')declare @SplitDatesTimes table (id int not null, dtPortion date, tmPortion time, SomeAttribute varchar(20))insert into @SplitDatesTimes (id, dtPortion, tmPortion, SomeAttribute)(select id, DATEADD(dd, 0, DATEDIFF(dd, 0, dt)), CONVERT(CHAR(12), DT,114), SomeAttribute from #DatesTimes) select ID, dtportion, tmportion, someattributefrom @SplitDatesTimeswhere dtportion>=@StartDate and dtPortion<=@EndDate and tmPortion>=@StartTime and tmPortion<@EndTimedrop table #DatesTimes[/CODE]The questions I have are, 1. Is there a simpler alternative 2. Do you think it's more efficient to push the data into a new table with split dates/times? |
|
Date and Time in where clause