HI, I have stored data & time as varchar type and data looks like this "9/16/2010 2:59:10 PM". Now I want to retrieve data from between two dates such as From 01/01/2010 to 31/10/2010, When I run this SQL Statement:
SELECT username, timein, timeout
FROM user_log
WHERE (timein BETWEEN '01/01/2010' AND '30/11/2010')
It returns me nothing. So How should I resolve this. Thanks.
You shouldn't expect date-based comparisons to work if the data isn't a real date, in terms of database data types. Your options are:
1) Upgrade the column into a date/time based data type.
2) As another user wrote, cast each string into a date. This option, unlike the first, will perform poorly.
If it's stored as a string, then treat it as a string. You can't compare the two strings 9/16/2010 and 30/11/2010 and expect anything useful (i.e. first digit 3 comes before 9). The first choice would be BETWEEN '1/1/2010' AND '11/30/2010'. If all your data is from one year it may work, though I'm not sure if the varying number of digits (not 0 padded) will work. With more than one year, 1/1/2010 is less than 2/1/2009 (first digit 1 comes before 2). If you had fixed width and started with the year (e.g. 2010-01-02), then it should work fine.
With the data you have, the best option is to cast each string to a date, then comparisons are easy. If you can somehow convert the field to datetime it would be best. In MS SQL Server you could create an indexed computed field which would convert the string to a date and index for fast querying.
HI, I have stored data & time as varchar type and data looks like this "9/16/2010 2:59:10 PM"
Don't do that. If your database has date/time types, use them! And if it doesn't (e.g., SQLite), then store your dates in ISO 8601 order (2010-09-16 14:59:10) so they'll sort correctly.