mssql - problem with select clause for records between two dates

[bhakti_thakkar]

hi,
i want to find a records that are between two dates including both the dates. below is my query but it doesnt works. no error message but the relevent record doesnt come even if the date is matching the condition. datatype of that field is datetime.



select t.TransactionCertificate_ID , t.DateOfIssue_DT , t.Project_ID , t.ITCType_TI from TransactionCertificate_T t where t.DateOfIssue_DT is not null and t.Deleted_DT is null and (t.TNTDHLtrackcode_VC ='' or t.TNTDHLtrackcode_VC is null) and ( t.DateOfIssue_DT between '02-12-09' and '02-12-09') order by t.DateOfIssue_DT



what is the problem. below is the record in the DB


29768 2009-02-12 16:28:04.000 4233 Organic EU2092/91 1
29801 2009-02-12 16:51:20.000 382 JAS 1


Please help if i give the date range as 02-12-09 and 02-13-09 it works but not when i give between '02-12-09' and '02-12-09'.

Thanks in advance

[Roelf]

when you don't specify a time in your query, MSSQL defaults to 0:00:00.000, so the query checks if there are records between two timestamps which are identical. The result is 0, as you already discovered. Try to add a time to the values in your query.

[bhakti_thakkar]

Let me check it and get back

[Old Pedant]

A possibly better way to do this:

... WHERE t.DateOfIssue_DT >= '02-12-2009'
AND t.DateOfIssue_DT < '02-13-2009'
...


Do you see why that works? Notice that I carefully used >= and then <

Again, recall that when you supply a DATE-ONLY value, the IMPLIED time is alwasy 0:00:00 (so far as I know, this is true for all DBs).

So that code is REALLY the equivalent of

... WHERE t.DateOfIssue_DT >= '02-12-2009 0:00:00'
AND t.DateOfIssue_DT < '02-13-2009 0:00:00'
...

And as you can see, that means it will pick up ALL records where the date-alone portion of the field is indeed 2/12/2009.

There *is* another way to do this:

... WHERE CONVERT( DATETIME, CONVERT( VARCHAR, t.DateOfIssue_DT, 112 ), 112 ) = '02-13-2009'

Yes, the double-convert is the only reasonable way in SQL Server (T-SQL) to convert a date-with-time value to a date-only value. It works fine. But...

But the problem is that now even if DateOfIssue_DT is INDEXED, SQL Server will have to do a full table scan or at least a full index scan. Because it doesn't understand what the function is doing, so it has to check *ALL* values.

If you use the other form I showed (with >= and < ) then SQL Server can take FULL ADVANTAGE of an indexed DateOfIssue_DT field and it can make a tremendous difference in performance. (If the field is not indexed, it likely won't make that much difference.)

Okay?