Filter data based on date in sql

Question

Im trying to execute the following SQL query and filter out the data based on the date.

I need to display a table which filters out the data such that, only those rows which are between the mentioned start_date and end_date

Here's the query that I have been trying

SELECT DISTINCT T1.column1, T1.column2, T2.START_DATE, T2.END_DATE
FROM Table1 T1, Table2 T2
WHERE (T1.column1= T2.column2) AND
(T2.START_DATE >= '15/01/2013 10:58:58' AND 
   T2.END_DATE <= '18/01/2013 10:58:58') ORDER BY T2.START_DATE DESC

I get the result with values from 2012 as well. Please help me out

Thanks

Fernald

Try using the ANSI format of 2013-01-15 10:58:58 and see how it goes – Bookman

Do you have errors in your data, are there END_DATEs that are before START_DATEs. If you added a where clause END_DATE > START_DATE does this fix the problem? – Nunatak

What is the data type for START_DATE and END_DATE columns ? – Fungus

@Bookman - I can't change now,will I be able to do that in varchar format only? I have checked if END_DATEs occur before START_DATEs There are no errors in the data; . Data type is VARCHAR(), I don't want to change that to date now; – Fernald

You should convert all to dates to compare. Then you may truncate your dates unless you need hour/second difference in your output. – Photojournalism

Answer 1

Since you have not mentioned about any errors, if START_DATE and END_DATE are DATETIME data type, there is nothing wrong with your query. If you are not getting the correct records, Please check the data.

However your date format may trouble you in a different server. There are some good practices you could adhere to avoid such issues.

-Whenever date is used as a string, try to use it in ISO or ISO8601 format (ie 'yyyymmdd' or 'yyyy-mm-ddThh:mi:ss.mmm')

-Also avoid joining tables with WHERE Table1, Table2 which is old and obsolete. JOINs are much better performed, neat and tidy.

You can change your query as follows;

SELECT DISTINCT T1.column1, T1.column2, T2.START_DATE, T2.END_DATE
FROM Table1 T1 JOIN Table2 T2 ON T1.column1 = T2.column2
WHERE (T2.START_DATE >= '20130115 10:58:58' AND 
       T2.END_DATE <= '20130118 10:58:58') 
ORDER BY T2.START_DATE DESC

Pedagogics

This issue is mainly because of the data types, the data type of both START_DATE and END_DATE is VARCHAR. Now i can't change the datatype..... I have to somehow deal with the string. Will I be able to do this? – Fernald

Can you show the format you have stored your dates in START_DATE and END_DATE fields? If they are in correct formats, you can Convert them to datetime before comparing. Ex; WHERE (CONVERT(datetime,T2.start_date) >= '20130115 10:58:58' AND CONVERT(datetime,T2.end_date) <= '20130118 10:58:58') – Pedagogics