Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Unanswered: converting string datatype to datetime datatype

    I have a column which has 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using

    update tablename
    set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' +
    SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));

    and then

    alter table tablename

    alter columnname datetime;

    but later it shows up the error

    Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    How do I change it any other opinion or any modification for the above query. Please help. Thank you.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @x varchar(25) = '05MAY2006:04:34:00.000000'
    
    SELECT @x
         , SubString(@x,1,2) + '-' + SubString(@x,3,3) + '-' + SubString(@x,6,4) + ' ' + SubString(@x,11,8)
         , SubString(@x, 1, 2)     --year
         + '-'
         + CASE SubString(@x, 3, 3)
             WHEN 'JAN' THEN '01'
             WHEN 'FEB' THEN '02'
             WHEN 'MAR' THEN '03'
             WHEN 'APR' THEN '04'
             WHEN 'MAY' THEN '05'
             WHEN 'JUN' THEN '06'
             WHEN 'JUL' THEN '07'
             WHEN 'AUG' THEN '08'
             WHEN 'SEP' THEN '09'
             WHEN 'OCT' THEN '10'
             WHEN 'NOV' THEN '11'
             WHEN 'DEC' THEN '12'  --month
             ELSE 'Uh-oh, spaghetti-o!'
           END
         + '-'
         + SubString(@x,  6, 4)    --day
         + ' '
         + SubString(@x, 11, 8)    --time
    Then have a play with IsDate() function: ISDATE (Transact-SQL)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    To get 6 digits of precision, you will need to use the datetime2 datatype, as well. Datetime only goes out to not-quite-milliseconds.

Posting Permissions

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