CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldate
gfuller40
gfuller40

Reputation: 1195

Function to go back 2 years, first day of last month

I'm hoping to find a solution for this to automate a report I have. Basically what I'm trying to accomplish here is grabbing a date (first day of previous month, two years ago through last day of previous month current year).

So the date span if running this month would look like this: between 4/1/2013 and 3/31/2015

I have found code to get the date two years ago but I'm not able to also incorporate the month functions... Any help is very much appreciated!

For year I'm using this:

SELECT CONVERT(VARCHAR(25),DATEADD(year,-2,GETDATE()),101)

Upvotes: 1

Views: 4124

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

Your where clause can look something like this:

where date >= cast(dateadd(year, -2,
                           dateadd(month, -1, getdate() - day(getdate()) + 1)
                          ) as date) and
      date < cast(getdate() - day(getdate()) + 1 as date)

This makes use of the handy convenience that subtracting/adding a number to a datetime is the same as adding a date. The start date says: get the first day of the month, then subtract one month, then subtract two years. This could have been done as dateadd(month, -25, . . .), but I think separating the logic is clearer.

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74317

Given a reference date (e.g. "today"),

declare @today date = '23 April 2015'

  • The 1st of the month is computed by subtracting 1 less than the day number of the current month:

    select first_of_current_month = dateadd(day,1-day(@today),@today)

  • The last day of the previous month is day 0 of the current month, so to get the last day of the previous month, just subtract the current day number:

    select last_of_previous_month = dateadd(day,-day(@today),@today)

  • Moving two years back is easy:

    select two_years_back = dateadd(year,-2, @today )

Putting it all together, this should do you:

declare @today date = '23 April 2015'

select *
  first_day_of_current_month =                  dateadd(day,1-day(@today),@today),
  last_day_of_previous_month =                  dateadd(day, -day(@today),@today)   ,
  date_from                  = dateadd(year,-2, dateadd(day,1-day(@today),@today) ) ,
  date_thru                  =                  dateadd(day, -day(@today),@today)

yielding the expected results:

first_day_of_current_month: 2015-04-01
last_day_of_previous_month: 2015-03-31  
date_from                 : 2013-04-01
date_thru                 : 2015-03-31

So you should be able to say something like this:

select *
from foo t
where t.transaction_date between dateadd(year,-2, dateadd(day,1-day(@today),@today) )
                             and dateadd(day, -day(@today),@today)

If you have to deal with datetime values rather than date, its easier to not use between and say something like this:

declare @today date = current_timestamp -- get the current date without a time component

select *
from foo t
where t.transaction_date >= dateadd(year,-2, dateadd(day,1-day(@today),@today) )
  and t.transaction_date <  dateadd(year, 0, dateadd(day, -day(@today),@today)

[superfluous addition of 0 years added for clarity]

Upvotes: 0

InitK
InitK

Reputation: 1291

This gives you two dates you are looking for: 

SELECT
    CAST((DATEADD(yy, -2, DATEADD(d, -1 * DATEPART(dd, getdate()) + 1 , GETDATE() ))) as date) as yourTwoYearsAgoDate,
    CAST((DATEADD(d, -1 * DATEPART(dd, GETDATE()), GETDATE())) as date) as yourEndOfLastMonthDate

Upvotes: 0

Wendy E
Wendy E

Reputation: 99

First day of previous month 2 years ago:

SELECT CONVERT(DATE,dateadd(day, -1, dateadd(day, 1 - day(GETDATE()), GETDATE())))

Last day of last month:

SELECT CONVERT(DATE,DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-2,GETDATE())), 0))

Then just do whatever logic you need with them

Upvotes: 1

Related Questions