Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    14

    Unanswered: Date difference issue with relational operator

    Date difference issue with relational operator

    convert(varchar(25),startDate,105)<convert(varchar (25),getDate() ,105)

    if the startDate = '26/11/2010' and getDate = '27/11/2010' - TRUE
    if the startDate = '29/11/2010' and getDate = '28/11/2010' - FALSE

    wherein with following dates, it returns - TRUE (ideally it should return FALSE)
    if the startDate = '01/12/2010' and getDate = '30/11/2010'

    Is this a date format issue..? If so, how to fix it ?
    Vikram RM

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You are comparing charactervalues, not date/numeric values. < or > gives totally different results when you use charactervalues.
    Why do you convert the startdate and enddate to a varchar before you compare them? Why not compare the datetime-values?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jan 2010
    Posts
    25
    even I had faced this issue... n later came to know that the characters values was being compared which actually takes ascii values into consideration, in such case the comparision is totally incorrection.

    But I still do not have solution if I just need to consider the date but not the timings. I remember the reason for which I used the convert function was to get rid of the timings and only to consider the date part.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    If you only assign date-values to a datetime-variable, the time-part does not matter in the comparison.
    If you have to datetimevariables, e.g. '3/1/2010 10:12:00' and '12/9/2011 08:30:03', you can convert the original value to char/varchar in the desired format (without the timepart) en then convert that value back to a datetime value.

    Those two converted datetimevalues you can compare properly.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    To truncate time from getdate() use
    Code:
    where startDate<dateadd(dd, datediff(dd, '', getdate()), '')

  6. #6
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    below query is not correct.

    dateadd(dd, datediff(dd, '', getdate()), '')

    Can any one please correct the same.

    Thanks in advance!!
    -PavanKumar M Reddy

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    ??
    Can you explain what you mean/want?
    Code:
    1> select dateadd(dd, datediff(dd, '', getdate()), '')
    2> go
                                
     -------------------------- 
            Jan 19 2011 12:00AM 
    
    (1 row affected)

Tags for this Thread

Posting Permissions

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