Subtracting From Time only

• sanjivus

  SSC Enthusiast

  Points: 156

  More actions

  October 7, 2002 at 10:59 am

  #79140

  Hi All,

  Please help me out. I have a column which is saving TIME in hh:mm:ss (0-24)format in a column having datatype text. I need to create an another column which should have a value "MYTIME- 1 hour". when i am doing the following :

  update MYTABLE set MYTIME = DateAdd(hour,-1,TIME)

  It is not doing what i want to do.

  Please help me out.

  Any help will be deeply appreciated.

  Thanks

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  October 7, 2002 at 11:33 am

  #437971

  The dateadd function requires a datetime or smalldatetime date type arguement, and you passed it a text datatype. Try something like this.

  create table mytable (time text)

  insert into mytable values('10:10:10')

  select * from mytable

  update mytable

  set time=convert(char(8),DateAdd(hour,-1,convert(datetime,cast(TIME as char),108)),108) from mytable

  select * from mytable

  drop table mytable

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP

• sanjivus

  SSC Enthusiast

  Points: 156

  More actions

  October 7, 2002 at 11:53 am

  #437975

  Hi Guru,

  I appreciate your quick and timely help, i was about to getting fired, you saved my job.

  A Bundle of Thanks.