SQL Date Formatting
bobs at twft.com
Thu Jan 12 11:29:55 CST 2012
On Jan 11, 2012, at 6:53 PM, Pete wrote:
> Hi Bob,
> I guess it depends on what you mean by "real dates" SQL date fields are
> pretty specific as to their format, that's why you give them a type of DATE
> or DATETIME. It's just a case of adjusting your SELECT statement to use
> the functions that SQL provides to return the date in whatever format you
> In SQLite, SELECT strftime(DateField,'%m/%d/%Y') returns the date in US
> In mySQL, SELECT DATE_FORMAT(DateField,'%m/%d/%Y)
> Or you can select the field as is and convert it in LC:
> set the itemdelimiter to "/"
> put item 2 of myDate & "/" & item 3 of myDate & "/" & item 1 of myDate
That is essentially what I did in my fromSQLDate and toSQLDate functions.
I wrote in another post (maybe I didn't send it) that I decided for simplicity's sake to keep the SQL dates the way they are, and present them to the user that way. I also decided to prevent the user from editing dates directly, instead opting for a date picker stack which provides a graphical date picker. This way the user cannot enter non-dates forcing me to validate the date fields. Simpler, cleaner, prettier.
When I need to work with the dates in Livecode, I run the dates through my conversion functions first. It would just be nice to have an option in the convert command. No big deal though.
More information about the use-livecode