CODEWITHSUNDEEP
sqlsql-serverdate
daniel aagentah
daniel aagentah

Reputation: 1702

SQL get First day of month 3 months before current Month

I am trying to select the first day of the month 3 months before the current date.

so for example if the current date was: '2015-11-08' my result would want to be: '2015-08-01'

I would prefer if this was in yyyy-mm-dd format.

I tried this to start with but had no luck:

SELECT DATEADD(dd, -DAY('2015-09-01') + 1, @today)

I have tried numerous things but cannot seem to crack it, any advice or help would be appreciated. Thank you in advance

Upvotes: 5

Views: 34712

Answers (2)

Roshan kamble
Roshan kamble

Reputation: 1

select dateadd(month, -3, dateadd(day, -2, '2014-09-03'))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270553

The logic is simple:

  • Subtract the day of the month minus 1 days from the date
  • Subtract three months

In SQL Server:

select dateadd(month, -3, dateadd(day, 1 - day(dte), dte))

For the current date:

select cast(dateadd(month, -3, dateadd(day, 1 - day(getdate()), getdate())) as date)

And as a string:

select convert(varchar(10),
               dateadd(month, -3, dateadd(day, 1 - day(getdate()), getdate())),
               120)

Upvotes: 20

Related Questions