GETDATE() - back one year

• akhlaq768

  SSChasing Mays

  Points: 642

  More actions

  October 28, 2010 at 5:18 am

  #228452

  Using the below query i get the following results

  2010-07-01 00:00:00.000

  2010-10-01 00:00:00.000

  How do i get the year to go back one year? so the results should be

  2009-07-01 00:00:00.000

  2009-10-01 00:00:00.000

  SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)

  SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

• sharath.chalamgari

  SSCertifiable

  Points: 5680

  More actions

  October 28, 2010 at 5:41 am

  #1243459

  Really did not understand your requirments why you are getting diffrence in months between 0 and the getdate and then adding back to it

  if you want your results then you can do like this:

  SELECT dateadd(year,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0))

  SELECT dateadd(year,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  October 28, 2010 at 5:41 am

  #1243460

  SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0))

  SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  October 28, 2010 at 5:41 am

  #1243461

  It's a simple change to your existing datetime expressions:

  SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 15, 0)

  SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)

• akhlaq768

  SSChasing Mays

  Points: 642

  More actions

  October 28, 2010 at 5:43 am

  #1243462

  thanks sharath.chalamgari

• sharath.chalamgari

  SSCertifiable

  Points: 5680

  More actions

  October 28, 2010 at 5:45 am

  #1243464

  for your information your 0 means "1900-01-01 00:00:00.000" in terms of datetime datatype

  akhlaq768 (10/28/2010)

  ---

  thanks sharath.chalamgari

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  October 28, 2010 at 12:27 pm

  #1243772

  sharath.chalamgari (10/28/2010)

  ---

  Really did not understand your requirments why you are getting diffrence in months between 0 and the getdate and then adding back to it

  This is a common way to get back the first day of the current month.

  Rob Schripsema
  Propack, Inc.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 28, 2010 at 9:04 pm

  #1243968

  What is wrong with this?

  declare @Date1 datetime, @Date2 datetime;

  set @Date1 = '2010-07-01 00:00:00.000'

  set @Date2 = '2010-10-01 00:00:00.000'

  select

  @Date1,

  @Date2,

  dateadd(yy, -1, @Date1),

  dateadd(yy, -1, @Date2)

  ;

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  October 28, 2010 at 10:07 pm

  #1243974

  select dateadd(yy, -1, getdate()) -- this is the easiest way to go back 1 year. same way for month: select dateadd(month, -1, getdate())

  Thanks