UPDATE on a smalldatetime field

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  October 31, 2006 at 9:17 am

  #170861

  Hello,

  This seems like it ought to be simple, but I'm getting a syntax error on the following UPDATE statment;

  UPDATE

  fmlog

  SET MONTH(fm_rk_trn_dte)= 8

  WHERE MONTH(fm_rk_trn_dte) = 9

  AND

  fm_rk_trn_dte BETWEEN '09/06/06' AND '09/15/06'

  I want to update the month portion of a smalldatetime field to 8 given the conditions in the WHERE clause. What am I missing?

  Thank you for your help.

  CSDunn

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 31, 2006 at 9:31 am

  #669006

  try this:

  UPDATE fmlog SET

      fm_rk_trn_dte = dateadd(mm, -1, fm_rk_trn_dte)

  WHERE

      MONTH(fm_rk_trn_dte) = 9

      AND fm_rk_trn_dte BETWEEN '2006-09-06' AND '2006-09-15'

   

  

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  October 31, 2006 at 9:36 am

  #669009

  yes, that just occured to me (duh).

  Thanks!