Post reply

Convert varchar to datetime

  • April 27, 2018 at 3:15 am

    #337424

    Hi,

    I'm getting 20180401 111843 varchar value from a table, and I have to convert it to date time. all the examples I saw on google doesn't solve my problem.

    Please help.

  • April 27, 2018 at 3:41 am

    #1988425

    If you use the STUFF function to insert a colon between the hours and minutes and minutes and seconds, you should then be able to convert to datetime.  If that doesn't work for you, please post the exact code that you use and the error message you get.

    John

  • April 27, 2018 at 4:29 am

    #1988426

    M'colleague is wise.

    Here - have some code.

    DECLARE @dt VARCHAR(20) = '20180426 112500';

    SELECT @dt AS YourString,
       STUFF(STUFF(@dt, 14, 0, ':'), 12, 0, ':') AS StuffedString,
       CONVERT(DATETIME, STUFF(STUFF(@dt, 14, 0, ':'), 12, 0, ':')) AS StuffedAndConvertedToDate;

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • April 27, 2018 at 6:27 am

    #1988435

    And change the data types so that you're storing date and time as date and time. Same thing goes for any other data type.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply