Subtracting 1 from a date value

• Claire Dubelbeis

  Newbie

  Points: 1

  More actions

  October 31, 2006 at 7:07 am

  #173280

  Can you help me?

  I'm trying to write some script to retreive a date value and then subtract 1 day from this and store the new value in a table.  For example, 31/10/2006 -1 = 30/10/2006.

  I know how to save the value once it is calculated, but don't know which calculation to use the acheive what I want.

  Any help is greatfully received.

   

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  October 31, 2006 at 7:24 am

  #668965

  DATEADD(day, -1, '31/10/2006')

  Far away is close at hand in the images of elsewhere.
  Anon.

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  October 31, 2006 at 10:24 am

  #669024

  select Cast('31/Oct/2006' as datetime) -1 would work too.

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  November 1, 2006 at 8:15 am

  #669219

  But you also need to look up the ISO-8601 format for temproal data so you will not use a local dialect

  Yes, I forgot to mention the format of the date for conversion

  Standard SQL uses only "yyyy-mm-dd"

  The only unambiguous format is yyyymmdd whereas nnnn-nn-nn (even if deemed to be yyyy-mm-dd) can be interpreted as yyyy-mm-dd or yyyy-dd-mm depending on the language settings of the login. SQL Server's default is the American format of yyyy-dd-mm.

  Far away is close at hand in the images of elsewhere.
  Anon.