Comparing different date formats

Discussion in 'MySQL' started by Wizard, Jul 31, 2007.

0
  1. #1
    One of the databases I just inherited has a field where is shows a warranty expiration date. However, the data is in different formats:
    10/31/2007
    6/30/2007
    2008-07-30

    And of course, the field where the date is stored is a VarChar.

    Are there any SQL functions that would be able to pull the different formats in a query? I've looked at several, but it looks like they need a consistent format.

    Thanks
    James
     
    Wizard, Jul 31, 2007 IP
    lezi_guy likes this.
  2. bluegrass special

    bluegrass special Peon

    Messages:
    790
    Likes Received:
    50
    Best Answers:
    0
    Trophy Points:
    0
    #2
    This works in MSSQL. I don't know if it will work in MySQL

    CAST(expDate AS datetime)
     
    bluegrass special, Jul 31, 2007 IP
  3. Wizard

    Wizard Member

    Messages:
    80
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #3
    Actually, I am using MSSQL, and the CAST function worked fine until I hit data that had characters in it and not any kind of date reference.

    There are alot of SPs in the dB and I went ahead and created a utility to change all the different date formats into one comon format. As I started digging into other dBs here, I have come across more date formats that are not consistent and will be cleaning those as well.

    What was the original programmer thinking when he created programs that wrote different date formats is beyond me!

    Thanks for the CAST info.
     
    Wizard, Aug 1, 2007 IP
  4. bluegrass special

    bluegrass special Peon

    Messages:
    790
    Likes Received:
    50
    Best Answers:
    0
    Trophy Points:
    0
    #4
    My guess is that the input was a textbox where the user got to dictate the format. Always a bad move.
     
    bluegrass special, Aug 1, 2007 IP
  5. Wizard

    Wizard Member

    Messages:
    80
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #5
    Actually, the programmer changed the date format in the code. The user had no control on how the date was inserted. His crappy coding actually is why I was hired! He left over a year ago, and the employees having been frustrated with the program (and it is used by almost all of the employees).

    Keeps me employed ;)
     
    Wizard, Aug 2, 2007 IP