Converting a numeric value to a DateTime

• jpalmer-755805

  SSC Eights!

  Points: 959

  More actions

  February 25, 2010 at 12:05 pm

  #137435

  I have data coming from an AS400 that stores date and time in a Numeric column. I need to convert this to an actual DateTime column in SQL.

  Example:

  20100224184500 --> 02/24/2010 18:45:00.000

  Does anyone have a solution to this dilema?

  Any help would be appreciated.

  Thanks,

  Jeff

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 25, 2010 at 12:16 pm

  #1125160

  Does the following ode help you out?

  declare @IntDate bigint,

  @CharDate varchar(32);

  set @IntDate = 20100224184500;

  set @CharDate = CAST(@IntDate as varchar(32));

  select @IntDate, @CharDate, cast(stuff(stuff(stuff(@CharDate,13,0,':'),11,0,':'),9,0,' ') as datetime);

• jpalmer-755805

  SSC Eights!

  Points: 959

  More actions

  February 25, 2010 at 12:24 pm

  #1125166

  Lynn,

  That worked perfectly.

  Thank you very much.

  Jeff