Date Calculation

Peter Haworth pete at lcsql.com
Thu Nov 29 21:51:16 EST 2012


Whoops, the weekday 0, weekday 1 and -7 days should all be in single quotes.

One of these days, I might try my hand at writing a
date calculation function that mimics the SQLite equivalent. They're very
powerful and easy to understand.  All you need is a base date and a series
of modifiers (days, hours, minutes, seconds, weeks, months, years, start of
year, start of day, start of month, localtime, utc, julianday, unixepoch)
to the base date.

Pete
lcSQL Software <http://www.lcsql.com>



On Thu, Nov 29, 2012 at 5:45 PM, Peter Haworth <pete at lcsql.com> wrote:

> OK, well I guess no sqlite database in use but just to illustrate, this is
> how easy this is with a SELECT statement
>
> Assuming the date is 11/28/12 per the original email, then to get the next
> Sunday:
>
> SELECT date('2012-11-28', weekday 0)
>
> To get the previous Monday:
>
> SELECT date('2012-11-28', -7 days,weekday 1)
>
> Pete
> lcSQL Software <http://www.lcsql.com>
>
>
>
> On Thu, Nov 29, 2012 at 1:23 PM, Mike Kerner <MikeKerner at roadrunner.com>wrote:
>
>> Just as an intellectual exercise I worked on shortening the script up
>> because the only way to fix the case is to add a bunch of breaks to it or
>> change the conditionals and order.  If you want to shorten it, here's one
>> option:
>>
>> *function* DetermineWeek pDate
>>
>>    *if* pDate is empty *then*
>>
>>       *put* the date into pDate
>>
>>    *end* *if* *# pDate is empty*
>>
>>    *convert* pDate to dateItems
>>
>>    *put* item -1 of pDate into dayNumber
>>
>>    *if* dayNumber=1 *then* *# Sunday, move to previous week*
>>
>>       theOffset=6
>>
>>    *else* *# Monday to Saturday*
>>
>>       theOffset=dayNumber-2
>>
>>    *end* *if* *# dayNumber=1*
>>
>>    *subtract* theOffset from item 3 of pDate
>>
>>    *convert* pDate to date
>>
>>    *return* pDate
>>
>> *end* DetermineWeek
>> _______________________________________________
>> 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
>>
>
>



More information about the use-livecode mailing list