SQL Date Formatting

[Kay C Lan]

On Fri, Jan 13, 2012 at 1:29 AM, Bob Sneidar <bobs at twft.com> wrote:

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.
>
>
I appreciate you've already achieved a suitable solution, but for the next
person that crosses this bridge it doesn't need to be as hard as you make
out.

Firstly, mySQL date format (yyyy-mm-dd hh:mm:ss) is one of the few sensible
date/time formats out there as it sorts quickly an easily. As far as I'm
concerned it is one of the few sensible date formats - LCs dateItems format
being the other.

Secondly, it is basically a one liner to get it ready for use by LCs
convert command.

A cursory glance at the above format will reveal it is exactly the same
order as used by LCs dateItems, and as the LC Dictionary says, once you get
date/times into dateItems (or seconds) they are invariant, this is super
important because it means they are identical on everyone's system,
regardless of OS, country they live, or individual preference. Brilliant!

You could:

put "2012-01-13 09:09:07" into tStore
replace "-" with comma in tStore
replace " " with comma in tStore
replace ":" with comma in tStore
put ",0" after tStore
-- dateItems must total 7

and have your date/time now in LC dateItems format, but I like one liners
so:

put "2012-01-13 09:09:07" into tStore
put replaceText(tStore,"[- :]",",") & ",0" into tStore
-- trailing 0 to make up 7 items
-- item 7 of dateItems can be 0 and LC will correct it when converted

basically the [- :] is a regex expression to look for - or [space] or :
then LC replaces each instance with a ,

Going the other way, turning LC dateItems in mySQL date/time is a one
liner, although very long (split here for viewing pleasure)

put the internet date into tStore
--just to take a string that doesn't look anything like a mySQL date
convert tStore to dateItems
--one line conversion from dateItems to mySQL date below
put item 1 of tStore & "-" & \
item 2 of tStore & "-" & \
item 3 of tStore & " " & \
item 4 of tStore & ":" & \
item 5 of tStore & ":" & \
item 6 of tStore into tStore
-- everything rearranged nicely to go into mySQL

Of course you are only working with dates and are not interested in times,
but here again is the beauty of LCs convert command, dateItems and LC's
forte - chunk expressions:

put the "13/1/12" into tStore
-- this NEEDS to match your system date so dd/mm/yy on mine
-- in your case you'll need a string that matches your system date format
convert tStore from system date to dateItems
--automatically rearranges the order and adds the extra 2 digits to the
year and 0s for hr,mm,sec (and a number representing the day of the week -
brilliant)
--one line conversion from dateItems to mySQL date below
put item 1 of tStore & "-" & \
item 2 of tStore & "-" & \
item 3 of tStore & " " & \
item 4 of tStore & ":" & \
item 5 of tStore & ":" & \
item 6 of tStore into tStore
-- everything rearranged nicely to go into mySQL
put word 1 of tStore into tJustTheDate
put word 2 of tStore into tJustTheTime

A couple of things to note:
1) If you insert 2012-1-2 3:4:5 into mySQL it will not hiccup, it will
automatically convert it to 2012-01-02 03:04:05 so don't bother creating a
bunch of code to format the leading zeros. Nice.

2) As pointed out by Mike, you can pre-format the data using mySQLs
DATE_FORMAT function - which will require you opening the mySQL manual, but
here are two that will automatically output either a date and time to LC
dateItems or just a date to LC dateItems:

DATE_FORMAT(dateNtime,'%Y,%m,%e,%k,%i,%s,0')
DATE_FORMAT(dateOnly,'%Y,%m,%e,0,0,0,0')

I love LCs dateItems and how it does everything automatically:

3) I love the fact I don't have to take into account if a user is using
mm/dd/yy format or dd-mm-yy format, once it converted to dateItems it's the
same on EVERY system.

4) When working with dateItems there MUST be 7 items, the great thing is,
if you don't know item 7 (the day of the week as a number) just use 0, when
you convert it to another format LC will automatically correct it.

5) Want to know what the date is 235 days from now; add 235 to item 3 of
dateItems, convert it to another format like long date and it's done for
you. Subtract 123532 hours from item 4, 113958735937 minutes from item 5,
etc etc it all just works. Even if you are just working with time, if you
have to do any maths with it, adding a fictitious date and doing the maths
with dateItems is a whole heap easier than the code to subtract 8762234 min
from 4:34PM by any other method.

6) Are you doing simple comparisons, ie day before, after or the same? Then
don't even bother with conversion; use mySQLs YEAR(), MONTH(), DAY(),
HOUR(), MINUTE(), SECOND() functions and directly compare them to the
appropriate item number in LC dateItems.

Although LC does not provide a mySQL format as one of the inbuilt formats
to convert to and from, the fact that dateItems is so cool,flexible, in the
same order and just a one liner away from mySQL it really shouldn't provoke
such F-I-TZ!

HTH