sql - Query to get the details for last 15 days from a PostgreSQL table

Question

I have a pre-existing PostgreSQL table called "Students" and a column named "Date of joining" in it along with other columns. Datatype of "Date of joining" column is varchar.

My requirement is to get the list of all students from the table who joined the class from last 15 days. I need to sort and get details from the table using "Date of joining" column. I'm not allowed to alter the datatype of the column to "date".

Table Sample

Desired output

I'm getting error while fetching the details since the column datatype is varchar.

Please help me to get the query for above scenario?

Thanks in Advance.

question from:https://stackoverflow.com/questions/66046522/query-to-get-the-details-for-last-15-days-from-a-postgresql-table

Answer 1

We cannot say how to solve this completely. But you can convert your column into a type date by using an appropriate date pattern. In this example the dates could like this: 2020-01-15. So the pattern to convert this into a date would be year-month-day which is YYYY-MM-DD. Any other pattern is possible.

SELECT
    *
FROM mytable
WHERE to_date(date_of_joining, 'YYYY-MM-DD') 
    BETWEEN CURRENT_DATE AND CURRENT_DATE - interval '15 days'

EDIT (user added sample data):

demo:db<>fiddle

In your case the date pattern would be: 'DD-Mon-YY'