Hello,
We have a database field where date is stored as DD-MON-YY . I have to write a query where I have to select records from MM1-YY1 to MM2-YY2.
The data in the table has the format DD-MON-YY. The data given by the request for the query is MON-YY.
The MM1-YY1 represents the first day of the given month. The MM2-YY2 is the last day of a month. My colleague has written a query defaulting as MM1-YY1 as always the first of every month. That's correct. She has written MM2-YY2 as always 31'st of every month. The bug is that the code does not work for months where there are less than 31 days. For example, February and June do not have 31 days.
I am concerned with manipulating the months, leap year et al, because they are both additional code and error prone logic. Is it possible to use the to_date with MM-YYYY when the column in the database has DD-MM-YYYY ?
I have pasted the current code to give a hint.
and svalid_to between to_date(''' || earliestExpiry || ''', ''YYYY-MM-DD'')
and to_date(''' || latestExpiry || ''', ''YYYY-MM-DD'') ';
Thanks