SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dates Problem - MS SQL

    Hi all, I have a problem when in one table I have dates in the following format, 2004-11-06 18:00:00.000, and in another table I have the date in this format, 2004-10-11 13:00:00.000.

    The problem here is that I have the Month and day values in different order (11 (Nov) represents the month values in the above examples), and as I am using the DATEDIFF function to determine the time difference between the date I am coming up with incorrect data.

    Is there a way I can get around this?

    Thanks

    Tryst

  2. #2
    SitePoint Guru hgilbert's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    839
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is strange
    i thought that DateFormat would be consistent throughout the database
    either American or British.

    you'd have to set the property of both tables to comply to the same formatting
    how to achieve that - i would have to reseach it myself - I'll try.


  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    tryst, dates are stored internally as integers, eg 36175

    what you are seeing is the default display format

    when you do date arithmetic, like DATEDIFF, the expressions are evaluated using the internal formats

    can you give examples of invalid results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    the problem has now been resolved.

    Its seems I was stored one date as a NVARCHAR data type, and another as an actual DATETIME data type. My fault

    Thanks

    Tryst


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
  •