locked
Conversion of datetime RRS feed

  • Question

  • Hi,

    i have a column with varchar(26) with values like '2002-09-05-14.23.32.818819'. I need to change it to datetime2(7) of this value. When i use CAST or CONVERT function, it is throwing error says out of range values. Is there any way to change the type to Datetime2(7) type or any other shortcut to do it. Kindly help to achive this. Thanks in advance!

    I want result like, i have added 0 at last digit as datetime2(7) will have 7 digits in the milliseconds

    2002-09-05 14:23:32.8188190

    Regards,

    Fazlu

    Wednesday, March 18, 2015 3:50 PM

Answers

  • Your varchar is in an invalid format. You'll have to adjust it manually:

    SELECT CAST(LEFT('2002-09-05-14.23.32.818819',10)+' '+REPLACE(LEFT(RIGHT('2002-09-05-14.23.32.818819',15),8),'.',':')+RIGHT('2002-09-05-14.23.32.818819',7) AS DATETIME2)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by Fazlu Thursday, March 19, 2015 5:17 AM
    Wednesday, March 18, 2015 4:02 PM
  • DECLARE @myDate varchar(26)
    SET @myDate = '2002-09-05-14.23.32.818819'
    
    SELECT CONVERT(datetime2(7), LEFT(@myDate, 10) + ' ' + REPLACE(SUBSTRING(@myDate, 12, 8), '.', ':') + SUBSTRING(@myDate, 20, LEN(@myDate) - 19))


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Charlie Liao Friday, March 20, 2015 5:30 AM
    Wednesday, March 18, 2015 4:19 PM
  • As long as your date strings hold that formatting, this should be what you're after.

    DECLARE @DateString VARCHAR(26) = '2002-09-05-14.23.32.818819'
    
    SELECT CAST(STUFF(STUFF(REPLACE(@DateString, '.',':'), 11, 1, ' '), 20, 1, '. ') AS DATETIME2(7))

    HTH,

    Jason


    Jason Long

    • Marked as answer by Charlie Liao Friday, March 20, 2015 5:30 AM
    Wednesday, March 18, 2015 9:45 PM

All replies

  • Your varchar is in an invalid format. You'll have to adjust it manually:

    SELECT CAST(LEFT('2002-09-05-14.23.32.818819',10)+' '+REPLACE(LEFT(RIGHT('2002-09-05-14.23.32.818819',15),8),'.',':')+RIGHT('2002-09-05-14.23.32.818819',7) AS DATETIME2)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by Fazlu Thursday, March 19, 2015 5:17 AM
    Wednesday, March 18, 2015 4:02 PM
  • DECLARE @myDate varchar(26)
    SET @myDate = '2002-09-05-14.23.32.818819'
    
    SELECT CONVERT(datetime2(7), LEFT(@myDate, 10) + ' ' + REPLACE(SUBSTRING(@myDate, 12, 8), '.', ':') + SUBSTRING(@myDate, 20, LEN(@myDate) - 19))


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Charlie Liao Friday, March 20, 2015 5:30 AM
    Wednesday, March 18, 2015 4:19 PM
  • As long as your date strings hold that formatting, this should be what you're after.

    DECLARE @DateString VARCHAR(26) = '2002-09-05-14.23.32.818819'
    
    SELECT CAST(STUFF(STUFF(REPLACE(@DateString, '.',':'), 11, 1, ' '), 20, 1, '. ') AS DATETIME2(7))

    HTH,

    Jason


    Jason Long

    • Marked as answer by Charlie Liao Friday, March 20, 2015 5:30 AM
    Wednesday, March 18, 2015 9:45 PM