Date manipulation and SQL....

[Johnkokk]

I've got an Access Database For whatever reason, there is a field in there for DATE that is declared as string. Also there are 3 other fields in there called FDAY,FMONTH,FYEAR that are declared as number and actually get the parsed values from the DATE field. So if the user enter for DATE 21/12/2009, the FDAY=21, FMONTH=12, FYEAR=2009 Now at some point, the user wants to get all the records from 20/6/2009 - 12/12/2009 Can someone help, on how we can make an SQL query to do that ? Whatever i have tried does not solve the problem. It is easy, as long you have the same month and year in the "FROM" and "TO", because you just check the FDAY variable to be within the limits. For example if we wanted from 5/10/2009 - 23/5/2009, the SQL would be "SELECT * FROM TABLE WHERE FDAY BETWEEN 5 AND 23" However the things get complicated when you have different month or year.For example if we want the records from 5/6/2009 to 23/11/2009. Now what ? Don't ask why the date field is declared as STRING and not DATE, in the first place. The question is, is it doable with what we have now ? Thanks.

[Luc Pattyn]

Hi, I would attempt this:

SELECT * FROM table WHERE STR_TO_DATE(stupidDateField, format)>=startDate AND STR_TO_DATE(stupidDateField, format) <=endDate

Where you need to replace "format" by whatever code suits your app, see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date[^] PS: it would be wise to rework your datebase so it stores real datetime information, not strings, as they will make life more difficult all the time. PS2: startDate and endDate, when literals, again make date formatting problems sneak into the situation; I am not sure parameterized SQL is possible with Access, if it is, you better use it. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Happy New Year to all.
We hope 2010 soon brings us automatic PRE tags!
Until then, please insert them manually.

---

[loyal ginger]

You can reformat the date to use ANSI format so they can be compared correctly. For example, use a query similar to this:

SELECT *
FROM Table
Where format(fyear,"0000")+'/'+format(fmonth,"00")+'/'+format(fday,"00")>='2009/06/05'
and format(fyear,"0000")+'/'+format(fmonth,"00")+'/'+format(fday,"00")<='2009/11/23';

Note that the begin and end date have to also be formatted to use ANSI format: "YYYY/MM/DD". It's not pretty but because you don't have control over your table structure, this may be the solution to your problem. Happy programming!

[Johnkokk]

Thanks, that did it !!! I should know better about this SQL :)