GETDATE() - back one year

Question

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)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

akhlaq768

SSChasing Mays

Points: 642

October 28, 2010 at 5:43 am

#1243462

thanks sharath.chalamgari 🙂

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 5

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 · Answer 6

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 · Answer 7

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 · Answer 8

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