Select today's date minus 6 months

Question

Select today's date minus 6 months

genova11

SSC-Addicted

Points: 434
More actions
June 20, 2019 at 7:04 pm

#3655888

I am pretty new to SQL Server. I have created date variables in Access but need to transfer them over to SQL. The syntax is different so I need a little push in the right direction.
I am doing a query that selects a date between 6 months from today and 1 month from today and puts both dates in yyyymm format. I used Format(DateAdd) in Access. I believe I can use DateAdd in SQL also but the rest of the syntax is different and I have not figured it out yet.
My end result would be:
Between 201812 and 201905.
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Yes you can use Dateadd in t-sql. With Dateadd, just use the interval or datepart as month and then pass in -1 to go back one month and -6 to go back six months. You can cast those results to date to get just the date portion without the time:

SELECT CAST(DATEADD(m, -1, GetDate()) as date)
SELECT CAST(DATEADD(m, -6, GetDate()) as date)

Here is the link to the help topic for Dateadd:

DATEADD (Transact-SQL)

Sue

rVadim Hall of Fame Points: 3969 More actions · Answer 2

What is the datatype of the date column in your table?

Does "Between 201812 and 201905" means include all days of Dec 2018 and all days of May 2019?

--Vadim R.

genova11 SSC-Addicted Points: 434 More actions · Answer 3

Thanks. This ended up working for me. I was close. Just had to change the mm to MM because I am on SQL Server 2016.

Between Format(DateAdd(MONTH,-6, GETDATE()), 'yyyyMM') And Format(DateAdd(MONTH,-1,GETDATE()), 'yyyyMM')