Help with date conversion

• Rob Sawnic

  SSC Eights!

  Points: 940

  More actions

  March 10, 2004 at 11:25 am

  #110647

  I have a datetime column. I want to update the time portion of the column with a value. The value I'll be getting is char(4) - like 0800. How do I update the column to include the time?

  Before:

  time_col......2004-07-02 00:00:00.000

  Update table set time_col = ?????

  Here's what I want the result to be:

  time_col......2004-07-02 08:00:00.000

  I'll be getting '0800' to use.

• Steve Henry-128072

  SSC Veteran

  Points: 261

  More actions

  March 10, 2004 at 1:32 pm

  #498179

  I'd use the CONVERT function on time_col to pick off the date, then concatenate the time to it...like this:

  -- @mytime is a variable that contains the formatted time, like 08:00.  You may be able to use unformatted military time as well, I didn't try it.

  -- style 101 in the convert tells SQL to just pick the date - see books online for more styles.

  update table set time_col = convert(varchar, time_col, 101) + @mytime

  Steve

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  March 10, 2004 at 3:45 pm

  #498190

  declare @t datetime, @h char(4)

  set @t = '20040702'

  set @h = '0800'

  select @t, @t + STUFF(@h,3,0,':')

  
  
  --Jonathan