SQL Date Formatting

[stephen barncard]

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
      set the itemDelimiter to comma

      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>