The problem with using the date as a string is that '19/3/2005' would fall into your range as would '23/1/1998'. It would be checking the range from an alphabetic point of view rather than a date one. Unless I've missed something here and I'm always happy to learn.
But since it was just the matter of retrieving records for that particular date range, i think usage of string to date function would be very much complicated for the newbie.... It was just a simple query then y we need to complicate it!...
Please reply for this and let me know if anything am missing on this... Thanks Shasank.
It's not a case of complicating things, but making them work. Comparing dates as if they were strings cannot work because they would be treated as a string and sorted alphabetically.
If the range is between '14/2/2005' and '27/2/2005' and you find a record with a stringed date of '20/1/1966' then the alphabetic comparisson would go like this...
'20' is greater then '14' and so the record comes after the start of the range.
'20' is less than '27' and so the record comes before the end of the range.
If the record is after the start and before the end of the range then it must be in the range and so you will have a record that, to the human eye, is not in the range; but to the computer's brain it is in the range.
There is no point in continuing with the compare as we already know that the record is in the range. So the month an year elements are ignored.
If you swapped things around and used strings that were in 'YYYY/M/DD' format, then the string compares would work - until you found an input record with a date in 'YYYY/MM/DD' or 'YYYY/M/D' format.
Using the STR_TO_DATE reduces the complexity by introducing a standard date format.
Sorry, I don't mean to rant but sorting things that are not strings as if they were is frought with problems and worth avoiding.