Post reply

Bizarre occurence with Date using BETWEEN function

  • February 10, 2005 at 12:23 pm

    #163341

    We have been testing some Quarterly Reports coding.  Basically calling records where dates fall between 10/01/2004 and 12/31/2004 or 01/10/2005 and 03/31/2005 or 04/01/2005 and 06/30/2005 or 07/01/2005 and 09/30/2005.  Basic fiscal quarters. 

    When we wrote WHERE DateOfInterest BETWEEN '10/01/2004' AND '09/30/2005' we got no records! 

    When we wrote WHERE DateOfInterest BETWEEN CONVERT( datetime, '10/01/2004') AND CONVERT( datetime, '09/30/2005') we got our entire recordset!   

    Shouldn't '01/01/2005' be an implicit conversion to datetime?  When we tried WHERE DateOfInterest BETWEEN '10/01/2004' AND '12/31/2005' OR DateOfInterest BETWEEN '01/01/2005' AND '09/30/2005' we got our entire recordset. 

    I can only suppose it is the change in year that is throwing this off...  any ideas or insight?   

    Thanks in advance. 

    I wasn't born stupid - I had to study.

  • February 10, 2005 at 12:29 pm

    #541646

    It sounds as though "DateOfInterest" is not a datetime field.  If not, then perhaps SQL is looking at them as:

    10012004 and 09302005   so the first number is actually bigger than the second number.

    Think of them like this .......... 10,012,004 and 09,302,005  and it's obvious it won't work.

     

  • February 10, 2005 at 12:35 pm

    #541648

    The outcome also depends on version of Sql Server, since Sql2K adheres more strictly to datatype precedence rules.

    Read the BOL section titled "Data Type Precedence" - as already stated, the answer to this issue depends on the actual datatype of column DateOfInterest

  • February 10, 2005 at 1:15 pm

    #541663

    Sorry, I was away for a bit.  It is a datetime datatype (I would have had to shoot myself if I had not checked that first... ).  I will start reading the Data Type Precedence, but as I stated, I would have assumed implicit conversion, hence precedence wouldn't necessarily be of issue....  But, this is a track I had not thought of, so I will look. 

    I wasn't born stupid - I had to study.

  • February 10, 2005 at 1:33 pm

    #541666

    I find that datetime comparisons are a mine field because your configuration of SQL can be set to accept M/D/Y or D/M/Y so 02/10/2005 can be 10th February or 2nd October.

    I have reached the stage where I pass dates into stored procedures as CHAR(10) in YYYY/MM/DD format and convert them into datetime fields for comparison. It is much safer than trying to guess the SQL configuration, plus it is more open should your solution have to be deployed on multiple servers.

    LinkedIn Profile

  • February 10, 2005 at 1:49 pm

    #541670

    When it comes to hard-coding datetime values, there is one format that will always work - regardless of any of the settings on the datebase: 'YYYYMMDD'. This is the recommended format for programming - but is maybe not so easy to understand for non-programmers.

    Just a hint - I definitely see that this is not a good approach in all cases.

    /olavho

  • February 11, 2005 at 2:37 am

    #541744

    I agree with Olav. I have also found the least problems with dates in the format YYYYMMDD. So your code could be WHERE DateOfInterest BETWEEN '20041001' AND '20050930'.

    Beware, however, that the end date, specified in this manner, is interpreted by SQL Server as '09/30/2005 00:00:00' so if you have any occurences on 30 Sept they will be excluded, except if you state the date as either '20050930 23h59' or '20051001'

    Schalk

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply