MSSQL query get data from 5 years back

Question

I´m currently working on a SQL statement that should filter the last 5 years data. The datatype that i wish to filter from is an "int"-column and the data is for example "201901" (year/month). I don´t want to have specific years/months in the query since it will be used every day/every year.

I´m stuck with how to write the query when the column is'nt a datetime datatype.

I tried with different dateadd/datefromparts but it doesnt work.

Answer 1

First convert to date type like so and then filter on it.

with ctedata as 
(
SELECT CONVERT(date, CONVERT(varchar(6), your_column) + '01') myDate
FROM TableName
)
select *
from ctedata 
where myDate >= dateadd(year, -5, GETDATE())

see answer below Converting Integer to Date

Answer 2

201901 is 201900 plus 1.

Going back 5 years is 201900 - 500 = 201400. You can use this criteria straight with a greater than comparison.

Or add 1 and use a greater equal comparison.

Answer 3

I would suggest to convert the query input into YYYYMM instead trying to convert the column into a date value.

Reason if you have an index on the column, the query will not be able to use that index because of the data conversion, but if you leave your column alone as it is and convert the query param to YYYYMM, this will be a sargble query and it will outperform any other possible solutions with your schema.

If you want you can have a date parameter for the query but do the following to match the datatype with column.

DECLARE @Date DATE = GETDATE();

SELECT *
FROM [TableName]
WHERE [YYYYMM_Col] >=  CAST(CONVERT(VARCHAR(6), DATEADD(YEAR, -5, @Date), 112) AS INT)
AND   [YYYYMM_Col] <=  CAST(CONVERT(VARCHAR(6), @Date, 112) AS INT);

Answer 4

Setting aside the fact that I hope you have a Date Dimension table with a corresponding INT field for Year/Month to join on for other date information, it's simply doing a subtraction of 5 years, padded with two zeros at the end for the "month":

WHERE t.YearMonth >= t.YearMonth - 500

However, you'd be better off with the actual date as an INT in your source table. Then, you can JOIN on that Data Dimension table (with the INT date as the PK). For example:

SELECT
    -- InvoiceDate is an INT, storing values such as 20190131 for 31-Jan-2019
    i.InvoiceDateKey
    d.FullDate AS InvoiceDate
FROM
    Finance.Invoices i
    INNER JOIN Common.Dates d ON i.InvoiceDate = d.DateKey
WHERE
    -- minus 5 years, with 4 padded zeros because DateKey has month and day
    d.DateKey >= d.DateKey - 50000

Answer 5

Another variation on the "use an INT as input" approach:

DECLARE @Date DATE = GETDATE();

SELECT *
FROM [TableName]
WHERE [YYYYMM_Col] >  (YEAR(@Date) - 5) * 100 + MONTH(@Date)
AND   [YYYYMM_Col] <=  YEAR(@Date) * 100 + MONTH(@Date)

Note that, although this works fine for complete numbers of years, it won't be suitable for 6 months / 18 month intervals.

Appropriate use of >, >=, < and <= will be down to specific requirements.

Answer 6

Forget conversions. Just use the DATEPART functions and some arithmetic

DECLARE @StartDate date = DATEADD(YEAR, -5, @Date);
DECLARE @EndDate date = GETDATE();

SELECT *
FROM TableName
WHERE YYYYMM_Col >= DATEPART(year, @StartDate) * 100 + DATEPART(month, @StartDate)
  AND YYYYMM_Col <= DATEPART(year, @EndDate) * 100 + DATEPART(month, @EndDate);

You should really store your dates in a proper date column.