SQL - update 'year' in a "date_time" field

Hello, Does anyone has updated only the YEAR in a "date_time" field? I'm using SQL on IDS 9.4. Thanks! Reyna _________________________________________________________ Reyna Sabina Phone: (305) 361-4324 NOAA/AOML/PHOD Fax: (305) 361-4392 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov Miami, FL 33149-1087 "Things do not get better by being left alone." -Winston Churchill

like this: update mytable set mydate = mydate + 1 units year; ________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] On Behalf Of Reyna.Sabina [Reyna.Sabina@noaa.gov] Sent: Thursday, January 07, 2010 5:12 PM To: ids@iiug.org Subject: SQL - update 'year' in a "date_time" field [18622] Hello, Does anyone has updated only the YEAR in a "date_time" field? I'm using SQL on IDS 9.4. Thanks! Reyna _________________________________________________________ Reyna Sabina Phone: (305) 361-4324 NOAA/AOML/PHOD Fax: (305) 361-4392 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov Miami, FL 33149-1087 "Things do not get better by being left alone." -Winston Churchill ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

update mytable set mydatetimecolumn = '2010' || substr( tochar( mydatetimecolumn ), 4 ) where keycol = <somevalue>; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 7, 2010 at 6:12 PM, Reyna.Sabina <Reyna.Sabina@noaa.gov> wrote: > Hello, > > Does anyone has updated only the YEAR in a "date_time" field? > > I'm using SQL on IDS 9.4. > > Thanks! > > Reyna > _________________________________________________________ > Reyna Sabina Phone: (305) 361-4324 > NOAA/AOML/PHOD Fax: (305) 361-4392 > 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov > Miami, FL 33149-1087 > > "Things do not get better by being left alone." > > -Winston Churchill > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce0099685541e047c9b6471

Assumption was you wanted them all adjusted the same amount up or down. update mytable set mydate = mydate + 1 units year; or add the quantifier "where extend(mydate, year to year) = '2009' or whatever ... ________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] On Behalf Of Reyna.Sabina [Reyna.Sabina@noaa.gov] Sent: Thursday, January 07, 2010 5:12 PM To: ids@iiug.org Subject: SQL - update 'year' in a "date_time" field [18622] Hello, Does anyone has updated only the YEAR in a "date_time" field? I'm using SQL on IDS 9.4. Thanks! Reyna _________________________________________________________ Reyna Sabina Phone: (305) 361-4324 NOAA/AOML/PHOD Fax: (305) 361-4392 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov Miami, FL 33149-1087 "Things do not get better by being left alone." -Winston Churchill ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.