Re: A date is a date (I wish)

[James Womeldorf <jwomeldo@FASTENAL.COM>]

Is there a way of doing that without hitting the database?
I have a set of date ranges and I am determining which dates are included in
all the ranges of the set.

E.g.
What dates (if any) occur in all three of the following ranges?
01-JAN-1999 - 03-FEB-2000
04-MAR-1997 - 09-APR-1999
05-FEB-1999 - 08-MAR-2001

Jim Womeldorf
Programmer/Analyst
Fastenal Company
jwomeldo@fastenal.com
(507) 453-8250


-----Original Message-----
From: Enis Koral [mailto:ekoral@ATT.NET]
Sent: Thursday, March 08, 2001 1:31 PM
To: SQR-USERS@list.iex.net
Subject: Re: A date is a date (I wish)


I am curious, why don't you use the Oracle date
functions for date arithmetic? (if you are on Oracle)
> Your procedure will work just fine if all you want to know is the less
than,
> greater than, equaltiy of the dates.  If you want to know the difference
in
> number of days, you will need to convert the julian equivalent into and
integer
> variable.
>
>
>
>
>
>
> James Womeldorf <jwomeldo@FASTENAL.COM> on 03/07/2001 06:29:25 PM
>
> Please respond to sqr-users@list.iex.net
>
> To:   SQR-USERS@list.iex.net
> cc:    (bcc: Rick Creel/IT/Aon Consulting)
>
> Subject:  Re: A date is a date (I wish)
>
>
>
> Thanks to Rod and Rick for their quick responses.  I propose to use the
> following procedure to compare dates.  (Sure wish you could write
functions
> in SQR so these things could be coded around neatly.)  Does anyone see
> problem with it?
>
> begin-procedure Date-Compare($Date1, $Date2, :$Comparison)
>         !Compares the dates of $Date1 and $Date2 (ignoring the time
portion)
>         ! and returns '<', '=', or '>' showing their relationship
>         Declare-variable
>                 date $Date1 $Date2
>         end-declare
>         let $julian_date1 = datetostr($date1,'YYYYDDD')
>         let $julian_date2 = datetostr($date2,'YYYYDDD')
>         if $julian_date1 < $julian_date2
>                 let $Comparison = '<'
>         else
>                 if $julian_date1 > $julian_date2
>                 let $Comparison = '>'
>             else
>                 let $Comparison = '='
>             end-if
>         end-if
> end-procedure
>
> Jim Womeldorf
> Programmer/Analyst
> Fastenal Company
> jwomeldo@fastenal.com
> (507) 453-8250
>
>
> -----Original Message-----
> From: Rod Wright [mailto:rwright@THEROUSECOMPANY.COM]
> Sent: Wednesday, March 07, 2001 3:14 PM
> To: SQR-USERS@list.iex.net
> Subject: Re: A date is a date (I wish)
>
>
> Hi Jim,
>
> There is a known error in SQR where it does not properly compare dates
when
> one
> is the end of a month and the next one is the beginning of the next month
> (your
> example). In order to get around this, I've converted dates to julian and
> then
> performed my compare. This works great. If you're on Oracle, I believe
that
> Oracle has a routine to do this. If you're using PeopleSoft, they have
> delivered
> routines in an SQC to perform this (they call it a serial date).
>
> Good luck,
> Rod
>
>
>
>
>
>
> James Womeldorf <jwomeldo@FASTENAL.COM> on 03/07/2001 04:01:45 PM
>
> Please respond to sqr-users@list.iex.net
>
> To:   SQR-USERS@list.iex.net
> cc:    (bcc: Rodney Wright/Trcb/Rouse)
>
> Subject:  A date is a date (I wish)
>
>
>
> Hi all,
> I am running
> SQR/4.3.4/PC/Windows NT 4.0/Oracle 7.3.4/Jun 03 1999
>
> I need to do a bunch of date comparisons in SQR so I said to myself, "SQR
> has a date type.  Let's just use that!."
>
> I proceeded to use Declare-Variable to declare variables as 'date' type
and
> assumed that elementary operations like '<' and '>' would work.
> Alas, it does not appear to be the case as can be seen by running the
> snippet below.
> Datediff seems to perform flawlessly and is probably the route I will have
> to go, but it bothers me that the simple comparisons do not work on 'date'
> types.
>
> Do any of you see any errors in my thinking or in my code?
> (If SQR really doesn't handle dates any better than this it SURE IS NICE
> THAT DATES IN TODAY'S DATA ARE MEANINGLESS!!!!)
>
> Thanks for your time.  I really enjoy this group.
> Jim
>
>
> begin-procedure Test Local
>     Declare-variable
>         Date $Start $End
>     End-declare
>     Let $Start = strtodate('01-SEP-2000')
>     let $End = strtodate('31-AUG-2000')
>     do Test-Date($Start, $End)
>     show ''
>     do Test-Date($End, $Start)
>     show ''
> end-procedure
>
> begin-procedure Test-Date($Start, $End)
>     declare-variable
>         date $Start $End
>     end-declare
>     show 'Start ' $Start
>     show 'End   ' $End
>     if $Start > $End
>         show '> comparison -> no days in range'
>     else
>         show '> comparison -> days in range'
>     end-if
>     if datediff($Start, $End, 'DAY') > 0
>         show 'datediff     -> no days in range'
>     else
>         show 'datediff     -> days in range'
>     end-if
> end-procedure
>
>
> Results in log file:
>
> Start 01-SEP-2000
> End   31-AUG-2000
> > comparison -> days in range
> datediff     -> no days in range
>
> Start 31-AUG-2000
> End   01-SEP-2000
> > comparison -> days in range
> datediff     -> days in range
>
>
>
> Jim Womeldorf
> Programmer/Analyst
> Fastenal Company
> jwomeldo@fastenal.com
> (507) 453-8250