SQL Date Formatting

[Bob Sneidar]

No you are right about it being a choice of one or the other. My functions only convert dates, so they are a lot simpler than yours, and I don't do any error checking. I ended up converting all my sql columns to date type and presenting my users with the sql format date. I then use a date picker stack that Marty Knapp made and I improved on slightly to alter dates. I decided to not let users edit dates directly. This actually has the distinct advantage of not having to validate what the user enters. 

Bob


On Jan 11, 2012, at 6:10 PM, stephen barncard wrote:

> Did I misunderstand your requirements, Bob?
> 
> On 11 January 2012 18:07, stephen barncard
> <stephenREVOLUTION2 at barncard.com>wrote:
> 
>> Really, this is no big deal. You can either convert at the Livecode end,
>> or if you don't need to sort by seconds, you could just make the date field
>> as a plain text field.  Also consider storing time as seconds in the
>> database, which is sortable.
>> 
>> The second handler I found useful too.
>> 
>> function convertSQLTime pSQLtime
>>      -- converts from below format to seconds
>>      -- and DATEITEMS
>>      -- if date is bogus then this returns empty
>>      -- 2005-10-28 01:07:04
>>      -- * the year
>>      --      * the month number
>>      --      * the day of the month
>>      --      * the hour in 24-hour time
>>      --      * the minute
>>      --      * the second
>> 
>>      get word 1 of pSQLTime
>>      set the itemDelimiter to "-"
>>      put item 1 of it & comma\
>>                  & item 2 of it & comma\
>>                  & item 3 of it & comma\
>>                  after tOut
>>      get word 2 of pSQLTime
>>      set the itemDelimiter to ":"
>>      put (item 1 of it) & comma\
>>                  & (item 2 of it) & comma\
>>                  & (item 3 of it) & comma\
>>                  & (5)\
>>                  after tOut
>>      convert tOut to seconds
>> 
>>      return tOut
>> end convertSQLTime
>> 
>> function PHPDateTimeName pOtherTime,pNums
>>       -- get unique number string - put -1 into pNums
>>       -- pNums can be a number that can be the char position to start
>> truncation
>>       -- if pNums is empty then the PHP/sql formatted date is returned
>>       if pOtherTime is empty then put the long time into pOtherTime
>>       set the numberFormat to "00"
>>       convert pOtherTime to dateitems -- 2005,10,9,17,50,43,1
>>       if pNums is not empty
>>       then
>>              get \
>>                            (item 1 of pOtherTime)+0 & (item 2 of
>> pOtherTime)+0 & (item 3 of pOtherTime)+0 & \
>>                            (item 4 of pOtherTime)+0 & (item 5 of
>> pOtherTime)+0 & (item 6 of pOtherTime)+0
>>       else
>>              get \
>>                            (item 1 of pOtherTime)+0 & "-" & (item 2 of
>> pOtherTime)+0 & "-" & (item 3 of pOtherTime)+0 && \
>>                            (item 4 of pOtherTime)+0 & ":" & (item 5 of
>> pOtherTime)+0 & ":" & (item 6 of pOtherTime)+0
>>       end if
>>       if pNums is a number then delete character pNums to -1 of it
>>       return it
>> end PHPDateTimeName
>> 
>> 
>> 
>> On 11 January 2012 17:20, Bob Sneidar <bobs at twft.com> wrote:
>> 
>>> Hi all. I am having F-I-TZ working with dates in mySQL. I WANT to store
>>> the dates as real dates, but mySQL formats them as yyyy-mm-dd. Livecode
>>> does no such thing and nothing I know how to do can get Livecode to work
>>> with dates in this format AS dates.
>>> 
>> 
>> 
>> Stephen Barncard
>> San Francisco Ca. USA
>> 
>> more about sqb  <http://www.google.com/profiles/sbarncar>
>> 
>> 
> 
> 
> -- 
> 
> 
> 
> Stephen Barncard
> San Francisco Ca. USA
> 
> more about sqb  <http://www.google.com/profiles/sbarncar>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode