Post reply

Convert string to datetime

  • April 12, 2009 at 3:46 pm

    #208314

    Hi all, i need a little help here:

    How i can convert the following string to the datetime type?

    '20081228012320'

    I am able to convert but only when i get date in a string and i need the time portion as well.

    Thanks

  • April 12, 2009 at 6:23 pm

    #976356

    trans54 (4/12/2009)

    Hi all, i need a little help here:

    How i can convert the following string to the datetime type?

    '20081228012320'

    I am able to convert but only when i get date in a string and i need the time portion as well.

    Thanks

    You need to put it into a format that SQL Server understands and then use cast or convert function. If all of your dates are in the above format, then you need to modify it to one of the following formats:

    '20081228 01:23:20'

    '2008-12-28T01:23:20'

    One way is to use the following:

    Select cast(stuff(stuff(stuff(@dateString, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') As datetime);

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 12, 2009 at 8:38 pm

    #976366

    Perfect, thank you so much!

  • April 12, 2009 at 11:07 pm

    #976391

    glad it worked - thanks for the feedback.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 12, 2009 at 11:23 pm

    #976393

    You can use the following code also.

    DECLARE @DateTimeValue varchar(32),

    @DateValue char(8),

    @TimeValue char(6)

    SELECT @DateValue = '20081228',

    @TimeValue = '012320'

    SELECT @DateTimeValue =

    convert(varchar, convert(datetime, @DateValue), 111)

    + ' ' + substring(@TimeValue, 1, 2)

    + ':' + substring(@TimeValue, 3, 2)

    + ':' + substring(@TimeValue, 5, 2)

    SELECT

    DateInput = @DateValue,

    TimeInput = @TimeValue,

    DateTimeOutput = @DateTimeValue;

    Source : http://www.sqlusa.com/bestpractices/datetimeconversion/

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

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