DATE minus month

• Shelley Allen

  SSCrazy

  Points: 2146

  More actions

  June 19, 2006 at 2:51 pm

  #62411

  I am working on a new billing cycle.  I will now bill on the first day of each month. 

  I get the first day of each month by the following:

  DECLARE @dt DATETIME

  DECLARE @dt2 VARCHAR(10)

  set @dt=(select CAST(LEFT(GETDATE()-DAY(GETDATE())+1,11) AS datetime))

  set @dt2=(select REPLACE(CONVERT(VARCHAR(10), @dt, 101), '/', '') AS datetime)

  select @dt2

  What I would like to know is how would I get the date minus Month.  For example, I would get the current date of 06012006 and want all transactions from the previous month. 

  Any ideas?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 19, 2006 at 3:27 pm

  #644703

  add the following to your snippet:

  declare @dt3 datetime

  set @dt3 = dateadd(mm, -1, @dt)

  select @dt, @dt3 --> 2006-06-01 00:00:00.000  2006-05-01 00:00:00.000

  a where clause selecting on dates would look something like this:

  where

   

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 19, 2006 at 3:29 pm

  #644705

  lost part of the post.  The where clause would be this:

  where billdate >= @dt3 and billdate < @dt

   

  again hth,

  Lynn

• Shelley Allen

  SSCrazy

  Points: 2146

  More actions

  June 20, 2006 at 7:04 am

  #644795

  Thank you