Comparing dates?

[John Machin]

On Feb 2, 10:07 pm, Gilles Ganault <nos... at nospam.com> wrote:
> On Mon, 02 Feb 2009 20:06:02 +1100, Ben Finney
>
> <bignose+hates-s... at benfinney.id.au> wrote:
> >The Python data types for date and time are in the ‘datetime’ module
> ><URL:http://www.python.org/doc/2.6/library/datetime>. Create a
> >‘datetime’ object for each value you want, then compare them.
>
> Thanks guys. For those interested, here's how to perform the
> conversion from DD/MM/YYYY to YYYY-MM-DD:
>
> =====
> import datetime
>
> connection = datetime.datetime.strptime("21/02/2008",
> "%d/%m/%Y").strftime("%Y-%m-%d")
> print connection



Gilles, that's certainly one way of doing it in Python.

Here's another:

>>> '-'.join(reversed('21/02/2008'.split('/')))
'2008-02-21'

Please consider the following:
If in fact the database has a text-type column (VARCHAR or similar)
that contains dates formatted as DD/MM/YYYY:

(1) somebody should be talking rather bluntly to the database
designer, and that column should be fixed if possible without breaking
other code

(2) doing the comparison on the client side (i.e. in Python) instead
of on the server side (i.e. in SQL) means that there will be
unnecessary data transmitted to the client side -- hence this should
only be considered if the volume of data is small. Imagine trying to
do a relational join using that column and another (normal) date
column by sucking both tables down to the client!

(3) it's quite possible to do the comparison in SQL:

e.g. if the column is named "d":

WHERE SUBSTR(d, 7, 4) || SUBSTR(d, 4, 2) || SUBSTR(d, 1, 2) <
'20090201' -- insert '-' if preferred
or something similar should be doable in any SQL implementation. Most
will have functions like str[pf]time that could be used to similar
effect.

Cheers,
John