Date Time SQL Selection Criteria

5 pts.
Tags:
Date Time
Selection Criteria
SQL Server
T/SQL
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.000 2009-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.
ASKED: October 30, 2009  2:22 PM
UPDATED: November 3, 2009  5:14 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this should do the trick:

<pre>SELECT * FROM tblTest
WHERE DATEADD(ss,CAST(LEFT(time,10) AS INT),’01/01/1970′)
BETWEEN ‘<some time>’ AND ‘<some time>’;</pre>

—————– kccrosser

While CarlosDL’s query will work, it will result in a full table scan on every call. A more efficient approach is to compute the time field’s min/max values and then run the query. Example (assume you want all records between 1/1/2009 and 1/31/2009, inclusive):

declare @timebase int
declare @mintime int
declare @maxtime int
set @timebase = CONVERT(DATETIME,’1970-01-01 00:00:00′)
set @mintime = DATEDIFF(SECOND, @timebase, CONVERT(DATETIME,’2009-01-01 00:00:00))
set @maxtime = DATEDIFF(SECOND, @timebase, CONVERT(DATETIME,’2009-02-01 00:00:00))

select * from tblTest
where time between @mintime and @maxtime

This approach can use an index on Time to efficiently query the data.

Whenever possible, avoid using functions (including data type conversions) in the Where clause, as these will nearly always cause the query optimizers to be unable to use indexes, and thus will result in table scans.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Yes, that would be more eficient. If the query is going to be run regularly, it is highly recommended to create the index (if it doesn't exist) and use kccrosser's approach. BTW, @timebase should be declared as DATETIME.
    65,110 pointsBadges:
    report
  • Kccrosser
    Oops - that's what happens when I type an answer without running it. <grin>
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following