Unlike VFPs date and datetime literals delimited with curly brackets and an additional ^character, i.e. {^yyyy-mm-dd}, other databases like MSSQL and MySQL indeed take a date string, as you have to have some literal representation of dates in queries, too. Dates are also displayed that way in workbench query results. But that doesn't mean dates and datetimes are stored as strings. You can only do LEFT() on string values and DATE_ADD()) etc. functions only work with date or datetime columns, not with string columns and I don't have to tell the choice of the right type is the only way to make use of corresponding type-dependent function and functionalities. For example, dates stored in char fields wouldn't sort correctly unless the string format is putting the year, month, and day in that order, which they usually don't.
MySQL is very "forgiving" or let's say versatile interpreting date strings as the correct date, but like in VFP you depend very much on server settings and internationally date strings differ in the order of day and month. Another reason to have date and datetime columns.
From the original question post I still can't imagine something like LEFT(datetimefield,10) works, as the LEFT() function is like VFPs LEFT() function needs a string. MYSQLs DATE() function is something FoxPro also has, but it's not FoxPRos DATE(), it is the equivalent to FoxPro's TTOD().
Yes, CURDATE() is the current date, but it's in string format. If you compute with it as in CURDATE()+2 it returns a number instead, so that's another problem you may only stumble upon later, if you try to calculate with it. I dislike such functions alone because of their overloaded and incoherent nature. I'm not against overloading, that can make sense, but not in this case.
I had a similar experience like you Mike, on the subject of currency and decided it is easier to store amounts as ints in cents/pennies instead of coping with conversion problems. But indeed it was for a quick fix and I later also learned to use connection options to use the currency data type, as it still makes sense to put data into the data types invented for them to have the full feature set of functionalities thought of around them.
Implicit conversions are a very usual topic and sometimes can help very much, but in MSSQL I also only like straight forward or setting independent conversions and regarding dates, there is an ISO string format 'YYYYMMDD' you can use in MSSQL, that's not working in MYSQL, though.
You can't transport VFP dates directly in a query, you have to parameterize a query, and with VFP that's done using SQLEXECs parameterization, not MYSQL, VFP converts that to ODBC compliant language the MySQL ODBC driver can convert to MySQL's way of parameterized queries and that's the principle with which VFPs parameterization can be used with any backend, so you can also do this:
Code:
Where DATE(`Time_on`) = ?(DATE())
What still speaks for MySQL's CURDATE or DATE(Now()) is that you likely also store CURDATE or NOW() into fields as default value and so even if the server is in another time zone than clients you can rely on this working while there is a shift in a few hours when the VFP client is in another time zone than the server. In the other hand you might want the day from the client perspective, then use VFPs DATE(), but then you can't use server-side CURDATE() or NOW() as default values as they can potentially put data one day off, then you will also need insert values with VFPs DATE() overriding the default value of the field.
There are always more things to think about, once you go from local DBFs to a server. It's easy to overlook some of these things, as you begin with localhost as a developer and have local time = server time by definition. And then I didn't even touch the aspect of daylight saving time and such things.
Bye, Olaf.
Olaf Doschke Software Engineering
