Update the time in already populate datetime field

• sc-w

  Hall of Fame

  Points: 3667

  More actions

  August 21, 2009 at 7:07 am

  #209802

  Hi,

  I have 2000 rows of data and they all have the date and time in them for field "EntryDate" I need to run a query to update all of the times from "00:00:00" to "06:00:00" normally i would use an update and replace query to update text fields which works great but with time it won't work. There are no errors and it says all the records have been updated but they still have 00:00:00

  any advice on this would be great. Thanks, oh and here is a sample to look at.

  use testdb

  CREATE TABLE T1(

  EntryDate datetime NOT NULL

  )

  INSERT INTO T1 VALUES('2009/08/19 00:00:00');

  SELECT EntryDate from T1

  --Query i tried to do the update!

  --update T1 set entrydate = replace(entrydate, '00:00:00','06:00:00')

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  August 21, 2009 at 7:49 am

  #1042834

  update T1 set entrydate = '2009/08/19 06:00:00'

  OR

  update T1 set entrydate = DATEADD( hh, 6, entrydate )

  CEWII

• sc-w

  Hall of Fame

  Points: 3667

  More actions

  August 21, 2009 at 8:15 am

  #1042856

  Thanks for that the second option worked a treat.