Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post

    mssql - problem with select clause for records between two dates

    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

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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.
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post
    Let me check it and get back

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    A possibly better way to do this:
    Code:
    ... 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
    Code:
    ... 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:
    Code:
    ... 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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •