October 30, 2009 at 7:59 am
Hi everyone,
My database table has a time field called [time], Data Type of "Numeric" length of "9" with a Precision of "14" a Scale of "0".
The query below produces the following output :
2009-10-14 00:02:42.0002009-10-28 20:15:10.000
Query :
select DATEADD(ss,CAST(Left(min(time),10) AS INT),'01/01/1970'),DATEADD(ss,CAST(Left(max(time),10) AS INT),'01/01/1970') from tblTest
Which is fine it has given me the min and max date/time in from records in the table.
How do I write a query to list the records where the [time] field is between a specific date and time ?
Your help would be much appreciated.
October 30, 2009 at 9:09 am
The complexity required for these statements is a pretty good example of why you should just leave time as a datetime field.
That said, you can use something like this:
select *
from tblTest
WHERE DATEADD(ss,CAST(Left(time,10) AS INT),'01/01/1970') BETWEEN @Start AND @End
October 30, 2009 at 9:18 am
frontpedal (10/30/2009)
Hi everyone,My database table has a time field called [time], Data Type of "Numeric" length of "9" with a Precision of "14" a Scale of "0".
The query below produces the following output :
2009-10-14 00:02:42.0002009-10-28 20:15:10.000
Query :
select DATEADD(ss,CAST(Left(min(time),10) AS INT),'01/01/1970'),DATEADD(ss,CAST(Left(max(time),10) AS INT),'01/01/1970') from tblTest
Which is fine it has given me the min and max date/time in from records in the table.
How do I write a query to list the records where the [time] field is between a specific date and time ?
Your help would be much appreciated.
I'm confused somewhat, why the LEFT if time is a numeric value?
October 30, 2009 at 9:24 am
Personally, i would calculate the number of seconds from 01jan1970 you are interested in and
select * from tblTest where time between X and Y
October 30, 2009 at 9:27 am
Dave Ballantyne (10/30/2009)
Personally, i would calculate the number of seconds from 01jan1970 you are interested in andselect * from tblTest where time between X and Y
OP,
If taking this extra step is an option, it has the potential to be better performing than the method I posted.
October 30, 2009 at 9:47 am
something like this:
declare @StartDate datetime,
@EndDate datetime;
set @StartDate = '2009-10-14';
set @EndDate = '2009-10-21';
select
*
from
where time between datediff(s,'1970-01-01', @StartDate) and datediff(s,'1970-01-01', @EndDate);
October 30, 2009 at 9:50 am
Lynn Pettis (10/30/2009)
something like this:
Would be spot on i would imagine 🙂
November 5, 2009 at 11:34 am
Thank you all for your advice. Much appreciated.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply