Date comparison in a text data field

[Ad Bec]

I have a database column field defined as a text. I store dates in format:
dd/mm/yyyy.

The user passes a Start date search string in the same format. I need to
filter for dates larger or equal then this string. Can this be done?

Thanks!

The db system i use is PostgreSQL with MX 7

[Dan Bracuk]

Yes, with string functions. It would be easier if you stored dates as dates, but you don't.

[Ad Bec]

Hi,

thanks for the answer Dan, Can you please tell me how can it be done with
string functions?

Here and example of the table:
ListingsSectionsSelectedValues.SectionCustomValue >= '28/12/2005'

Thanks for your help!

[mxstu]

I agree that it would be much better to store the date values in a date/time
column. Have you tried simply casting the column as a date/time in your query?
I don't know the correct syntax for PostgreSQL, but something like

WHERE CAST(yourColumn AS datetime) >= (some date value)

[Dan Bracuk]

I don't know postrgresql, but, what you have to do is re-arrange your string to
yyyymmdd. To do that in Cold fusion with the user input is simple -
x =right(date, 4) & mid(date, 3, 2) & left(date, 2);

If you can't do something similar in your database, you just put

where (your postgresql function stuff) < '#x#'

Otherwise, you could always select all rows (hope there are not that many,
loop though and rearrange the field using querysetcell and do a Q of Q. It
wouldn't be efficient, but it would get your answer.



Originally posted by: Ad Bec
Hi,

thanks for the answer Dan, Can you please tell me how can it be done with
string functions?

Here and example of the table:
ListingsSectionsSelectedValues.SectionCustomValue >= '28/12/2005'

Thanks for your help!

[mxstu]

>To do that in Cold fusion with the user input is simple -

>x =right(date, 4) & mid(date, 3, 2) & left(date, 2);

Just a small correction. It should be:

x =right(date, 4) & mid(date, 4, 2) & left(date, 2);