[thelist] SQL Date formating

[rudy]

>   SELECT *
>     FROM 100420_gmtr_t
>        where read_date
>             like 'DATE_FORMAT(12/01/2002, %Y-%m-%d)%'

hi chris

dates are tough, eh

i assume you are using LIKE in order to truncate times

one reason it's not working is because the string 'DATE_FO...' will be
interpreted as just that very string, i.e. the DATE_FORMAT function isn't
evaluated

but that's not all

i see that Beam has already given you the answer, but hey, it took me a
while to write this so i'm going to send it anyway

this will be a long explanation, so you may want to grab a coffee

;o)

the internal representation of a datetime value is usually a pair of
integers, one for the date (in days) and one for the time (in seconds or
milliseconds)

i believe mysql is the same as other database systems in this regard (there
is uncertainty because mysql allows "certain 'not strictly' legal date
values, for example 1999-11-31")

databases have complex logic for evaluating date expressions, and converting
between internal integer representations and external string representations

in date comparisons, the database can do integer comparisons internally, and
this is generally faster, especially if each row's value does not require
converting

in the type of condition you have, i think all databases would be forced to
do a string comparison, since you are using LIKE

the database thinks "oh, okay, i'll convert read_date to a string, no prob"

so it converts the internal datetime value of read_date to a string, using
its default date formatting algorithm, since you did not explicitly say what
format to use for read_date

so it might come out as 2002-01-12 or some other format which doesn't match
the LIKE string you provided, even assuming you took the outer quotes off so
that DATE_FORMAT could create its string

so you don't select any rows at all

<tip type="sql">
guessing which default date format the database will use is a crap shoot
</tip>

now look at Beam's approach --

        where DATE_FORMAT(read_date, %Y-%m-%d)
           = '2002-01-12'

this explicity converts into a string only the date part of read_date, i.e.
not the time, which means you do not have to mess around with LIKE, and can
use an equality test instead

notice it's a string comparison so your literal '2002-01-12' has to have the
dashes because that's how read_date is being formatted

as i suggested, letting the database do an internal comparison rather than a
string comparison is usually the better approach, not only for performance,
but also because the sql to do it is often simpler

and simpler is gooder

the challenge, of course, is to handle the specific requirement

your example seeks all read_date values for a specific date

a first attempt at a date comparison might be

    where read_date = '12/01/2002'

this will almost always allow the database to decide to convert the literal
into the internal representation of a datetime value

as an aside, the database is pretty good at this, because this is the way
that you normally provide date values, for example in the INSERT
statement --

   insert into foo ( bar , somedate )
            values ( 'heh' , '2003-02-11' )

i think mysql wants to have the year always stated first in date literals,
so keep that in mind, yours was the wrong format

however, there still may be a problem that will affect your particular
situation

if read_date is a datetime field, and your application allows datetime
values to be inserted with time parts, then your query may "work" but
not deliver what you want

so in this first attempt --

    where read_date = '2002-01-12'

when the literal is converted to a datetime value, it will invariably get a
time part of midnight

if read_date contains dates and times, such as it would if you were stamping
rows with date and time of insertion or update, the only rows you'd get back
would be rows where the time part just happened to be midnight

you could design the application so that all datetime values have no time
part (in mysql i believe you could use the non-standard DATE datatype for
this purpose), but this is hard to do as a retrofit

what you can do is write sql that compares just date parts

in this regard, the Beam solution above, which works, i hasten to point out,
might not run very well because it is a string comparison and requires that
each row's read_date value be converted

an alternative approach is to utilize a builtin function, if one exists, to
extract just the date part of a datetime value

an example i have seen in mysql is

   where to_days(read_date) = to_days('2002-01-12')

this explicitly converts the date to an integer

recall that the internal representation of a datetime value has the
actual day number as the first integer

thus -- and i'm guessing here, but it's an educated guess -- there is no
actual conversion of read_date values required

the expression  to_days('2002-01-12')  would of course be converted once, at
the time the query is parsed, and then the comparison could proceed on
read_date with no conversion

i'm not certain that mysql works that way, i merely hope that it does

this might seem like splitting hairs, but careful consideration of sql can
be very important in dealing with large tables

keep this sensitivity to the date conversion issue in mind in any query that
deals with date expressions

foir example, if you write

    where year(read_date) = 2002

this allows the database to recognize this as an opportunity to
compare the days integer to bounding values -- the optimizer would
create a day integer for the first day of 2002, and another for the last day
of 2002, and then do a BETWEEN test on the date part of read_date, in order
to optimize retrieval by avoiding conversion, because the date part of the
read_date column is already a days integer

if on the other hand you said

    where DATE_FORMAT(read_date,'%Y') = '2002'

then a conversion must be done to do the explicit string comparison


as i said, dates are tough, eh


rudy