Select today's date minus 6 months

  • 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.

  • 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

     

    Sue

  • 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.

  • 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')

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

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