Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: Cast and convert to DATETIME type

    Hi

    I am trying to get my head around on the following confusing output types.
    Hope someone here got the explanation.

    Following code when run in Query analyser will out puts '2007-10-01 00:00:00.000'
    When the same code run in MS visual studio 2008 in sql debugging mode
    the out put of the cast taken into a variable would display it as
    '01/Oct/2007 00:00:00.000 AM'

    SELECT CAST('01/Oct/2007' AS DATETIME)

    Thanks in advance.
    TS

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    this is just a question of display format. The actual date is not stored in the way shown, but as an 8-byte number (or set of numbers). Point is, the display format is irrelevant.

  3. #3
    Join Date
    Feb 2009
    Posts
    51

    slight error in my post

    There was a slight error in my previous post in MS VS08 SQL debugger the date was given as 01/10/2007 00:00:00 AM (not 01/Oct/2007 00:00:00).T

    The confusion occurred when the sql code ran in query analyser
    SELECT * FROM MEMBER_DET WHERE JOIN_DATE = CAST('01/Oct/2007' AS DATETIME)
    would return data

    But sql code copied from MS VS08 debug windows as follows ran in query analyser gave a date time mismatch error.
    SELECT * FROM MEMBER_DET WHERE JOIN_DATE = CAST('01/10/2007' AS DATETIME)

    It sort of mislead me into checking all the ate conversions etc... but actually it weren’t the error.
    Best Regards
    TS

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The default language assume a string to be 'mm/dd/ccyy'
    Use the convert function instead and specify the date style
    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
    Code:
    select convert(datetime,'01/10/2007',101), convert(datetime,'01/10/2007',103)
                            
    ----------------------- -----------------------
    2007-01-10 00:00:00.000 2007-10-01 00:00:00.000
    
    (1 row(s) affected)

  5. #5
    Join Date
    Dec 2008
    Posts
    135

Posting Permissions

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