Convert string to datetime

• trans54

  Ten Centuries

  Points: 1357

  More actions

  Follow

  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

• Jeffrey Williams

  SSC Guru

  Points: 90345

  More actions

  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
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

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

• trans54

  Ten Centuries

  Points: 1357

  More actions

  Follow

  April 12, 2009 at 8:38 pm

  #976366

  Perfect, thank you so much!

• Jeffrey Williams

  SSC Guru

  Points: 90345

  More actions

  April 12, 2009 at 11:07 pm

  #976391

  glad it worked - thanks for the feedback.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

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

• Kavin-1064454

  SSC Veteran

  Points: 260

  More actions

  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/