Peter Theobald said:
>What exactly is the difference between the different date types?
Nothing. Just different ways of representing the same data to different
precision. A timestamp (ts) includes hour/minute/second whereas a small
date (d) includes only the year/month/day itself. Like the difference
between and integer and a real (float).
>I tried inserting the actual string {ts '2000-09-02 00:00:00'}
>and it does NOT work, so a date value is not just a string that
>represents a date.
Um, actually, yeah it is. :) Did you try the insert with a real SQL client
or did you try it with CF? I'm willing to bet that if you tried it with CF
then you ran into the same issue I posted about earlier today: CF escaped
your single-quotes and turned it into {ts ''2000-09-02 00:00:00''}, which is
illegal. Try wrapping it in a PreserveSingleQuotes().
>I tried inserting the string "2 Sep 2000" and it works fine...
>so why do I need all these date functions?
Because some SQL engines are smarter about dates than others. Some can take
just about any human-readable date format and parse it. Others have to have
it in a certain form or you're toast. IIRC, older versions of Access
required you to wrap the dates in hashes (#08/31/2000#) instead of single
quotes. The plethora (not of balloons, Jefe, but) of date functions and
formats is to help accomodate the different engines. I seem to remember
having a bunch of scripts originally written for Access, then migrated to
Sybase XI ODBC, then Sybase XI Native, then SQL Server ODBC, then to SQL
Server OLEDB, (it was really good code) and I know we had to go through and
change the dates in all of our queries at least twice. (Yet again IIRC, the
Sybase Native drivers have horrible date parsers.)
Hope that clears it up. :)
-Rick
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
