locked
Converting A String to Date Datatype RRS feed

  • Question

  • User-266149547 posted

    I need to convert a number field that contains a date in the following format: (mm/dd/yy) to an actual date datatype.

    For example,

    number field - 60181 converted to date field 06/01/81

    I tried just by changing the datatype from number to date, but the conversion is wrong, it gives me 10/6/2064.

    Thanks for any help.

    Tuesday, January 27, 2009 2:53 PM

Answers

  • User77042963 posted

    SELECT dtNum,


     DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) AS NewDate

    ,FormatDateTime( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2))   ) as  'MM/DD/YYYY'

    ,Format( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) ,'MM/DD/YY'   ) as 'MM/DD/YY'


    FROM Table1;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 27, 2009 3:24 PM

All replies

  • User77042963 posted

    SELECT dtNum,


     DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) AS NewDate

    ,FormatDateTime( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2))   ) as  'MM/DD/YYYY'

    ,Format( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) ,'MM/DD/YY'   ) as 'MM/DD/YY'


    FROM Table1;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 27, 2009 3:24 PM
  • User-266149547 posted

    Thank you.  That worked perfectly.

    Tuesday, January 27, 2009 4:13 PM
  • User-1199946673 posted

    And how would you convert 12181?

    is this 12/01/81 or 1/21/81?

    Tuesday, January 27, 2009 4:21 PM
  • User-266149547 posted

    That is a good question. 

    But I believe 12181 would be 1/21/81

    and 120181 would be 12/01/81.

    Wednesday, January 28, 2009 9:10 AM