Re: A date is a date (I wish)

[Enis Koral <ekoral@ATT.NET>]

If you use any of the database date functions, you have
to hit the database. But you can minimize the hit.
I would create a temp table with date ranges and join
that with the actual employee data table.
> 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