On Fri, Jan 13, 2012 at 1:29 AM, Bob Sneidar <b...@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 _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
