SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sql Server 2000 - Comparing string dates..

    Hi all,

    Im trying to query a table and compare dates, the date field is a varchar, I have the following:

    Code:
    WHERE
    CAST(REPLACE(a.[Date], '/', '') AS Integer) < CAST(REPLACE('21/07/2004', '/', '') AS Integer)
    So what im trying to do is strip the /, then compare the sizes of the numbers, but it doesn't seem to be working, its returning a record with date of 21/07/2004 and one of 20/07/2004 and not one of 04/09/2004.. (only 3 records)

    I dont understand..

    Any SQL guru's out there?

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there is a problem with your logic. the problem is that your converting the dates to integers but the numbers don't line up the way your thinking

    21/07/2004 becomes 21,072,004 and
    04/09/2004 becomes 04,092,004

    as dates, the latter is later but those integer representations betray that fact.

    simple solution would be to cast to datetime instead of integer. (caveat: use the right cultural formatting options, your above examples are in european format which may or may not be what your sql server will be expecting. be sure to test)

    if for whatever reason you need to do the comparison as ints, then you need to string mapinupate the hell out of the values til they match yyyymmdd, which puts the most significant digits first.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for clearing that up CrowDozer, I knew there was something wrong with the logic, was suffering from brain lag yesterday hehe

    I'll get that fixed right now, Thanks again for the help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by matt-
    Any SQL guru's out there?
    yes

    aside: why is it a varchar?


    WHERE CAST([Date] AS datetime) < '21/07/2004'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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