I have a database table which is named as table and there is a column Col which is present in table with datatype varchar. Column Col contains dates in the format MMM-YY. For example Col has values as :

As, we can see data can be duplicated like DEC-21. I want to extract last 6 months data based on the recent month present in Col. For example, If the DEC-21 is the most recent date(consider, day is not present) and so, I want data from DEC-21 to JUN-21 i.e. 12-21 to 06-21 if we map DEC to 12 and JUN to 06. This Table has many columns and one of the columns is Col which I mentioned above and I have to extract data based on the column Col by using SQL query.

I have written a query as:

SELECT *
FROM table
WHERE CAST(RIGHT(Col,4) AS INT) Between 2020 and 2021

But here I get data between 2020 and 2021. So, By doing some modification in the above query or Is there any other way to get the past 6 months data from the recent date which is in MMM-YYYY format from Col column.
I was writing code in R and I was using dbGetQuery() where I have to pass the SQL query. I have already done this thing after storing it in a dataframe but is there any way to do it directly by sql query ?
Any help would be appreciated.

CodePudding user response：

Something like this should work.

SELECT * FROM table where CONVERT(DATE,'01-' Col) BETWEEN '01-Jun-2021' and '31-Dec-2021'

CodePudding user response：

with data as (
    select *,
        convert(date, '01-'   dt, 105) as converted_dt,
        max(convert(date, '01-'   dt, 105)) over () as last_converted_dt
    from T
)
select * from data
where converted_dt >= dateadd(month, -6, last_converted_dt);

The 105 comes from the list of date formats which can be found in the documentation for cast/convert. SQL Server can convert strings like Apr 2021 so a cast like below might also work (if you actually have four-digit years) but it's best to be explicit about the format as I did above.

cast(replace(dt, '-', ' ' as date)