Post reply

Converting varchar to datetime T-SQL.

  • June 16, 2017 at 1:33 pm

    #343275

    Hello,

    I have 2 column first column in this Format YYYYMMDD and second column as HHMM i want one column as datetime.

    First column as = 20170612
    Second column = 1345

    I want date time as 2017-06-12 13:45

    Thank you.

  • June 16, 2017 at 1:41 pm

    #1947151

    sks_989 - Friday, June 16, 2017 1:33 PM

    Hello,

    I have 2 column first column in this Format YYYYMMDD and second column as HHMM i want one column as datetime.

    First column as = 20170612
    Second column = 1345

    I want date time as 2017-06-12 13:45

    Thank you.

    One question, what happens with times before 10AM? Is there a leading zero or no?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 16, 2017 at 1:45 pm

    #1947152

    there will be leading 0 like this way 0930 or 0700

  • June 16, 2017 at 2:21 pm

    #1947156

    sks_989 - Friday, June 16, 2017 1:45 PM

    there will be leading 0 like this way 0930 or 0700

    Then it's easy. Here's an example on how to do it.

    DECLARE @Sample TABLE(
      DateCol char(8),
      TimeCol char(4),
      DatetimeCol datetime
    )
    INSERT INTO @Sample VALUES( '20170612', '1345', NULL)

    SELECT *, CAST( DateCol + ' ' + STUFF( TimeCol, 3, 0, ':') AS datetime)
    FROM @Sample;

    UPDATE @Sample SET
      DatetimeCol = CAST( DateCol + ' ' + STUFF( TimeCol, 3, 0, ':') AS datetime)

    SELECT *
    FROM @Sample

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 19, 2017 at 12:34 pm

    #1947305

    Thanks Luis it worked.

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

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