Re: Date comparisons

[Julie Lawton <lawton_julie@HOTMAIL.COM>]

Hi Tim,

A very strange thing has happened.  Last week you helped me with date
comparison problem which I was having.  I modified my sqr as you suggested
and all worked well.  I know that for sure, as I still have a sample file
which I generated at the time.

I haven't touched it since until today.  I ran it again today and I have a
problem with one record.  I know the data hasn't changed (as it is test data
and the dates/times are the same as last Tuesday).  On this one record, when
I query the two dates in SQL, I get:

SystemCreationDateTime Mar  7 2000  8:55AM
SystemModificationDatetime Mar  7 2000  9:33AM

But when my sqr file selects this date, I get:

SystemCreationDateTime Mar  7 2000  9:33:58:000AM
SystemModificationDateTime Mar  7 2000 8:55:20:000AM

When it does the:

edit(&SystemCreationDateTime,'YYYY|MM|DD|HH|MM')

I get:

20000307.00 and 20000307.00 for the SystemModificationDatetime

It appears to have completely ignored the time portion.  I have another
record which works correctly.  I know this worked correctly last week.  Why
would this not edit the format of the date correctly for one record when it
obviously knows the full date?

I would appreciate any advice you could give.

Thanks

Julie


>From: Tim_Green@NAS.ADP.COM
>Reply-To: sqr-users@list.iex.net
>To: SQR-USERS@list.iex.net
>Subject: Re: Date comparisons
>Date: Tue, 7 Mar 2000 09:16:17 -0700
>
>If memory serves, datediff as an SQR function was introduced on version 4,
>hence your error when attempting to use it.  Prior to version 4 of SQR,
>dates were converted and stored as strings in SQR variables, using the
>default database date format.  If the default date string format is not
>string comparision friendly (for instance, Sybase and SQL Server default to
>"Mon DD, YYYY" and Oracle "DD-Mon-YYYY", neither of which compare properly
>as strings ), you need to convert the date when you select it so that it
>will compare properly or convert it after it's selected but before the
>comparison.
>
>If you want the code to be db platform independent, try using SQR's edit
>function.  For example:
>
>      let $mod_dt = edit( &SystemModificationDateTime, 'YYYY|MM|DD|HH|MM')
>      let $cr_dt  = edit( &SystemCreationDateTime, 'YYYY|MM|DD|HH|MM')
>
>      if #status = 1 and $mod_dt > $cr_dt
>           print 'A' ()
>           ...
>
>or you could convert the dates to strings in the format you want with the
>select statement.  The syntax will be dependent on your database platform.
>
>I might have the syntax slightly wrong on the example above (I haven't
>tested it), but you get the idea.
>
>Good luck,
>Tim
>
>
>
>
>
>
>Julie Lawton <lawton_julie@HOTMAIL.COM>@list.iex.net> on 03/07/2000
>10:33:56 AM
>
>Please respond to sqr-users@list.iex.net
>
>Sent by:  "Discussion of SQR, Brio Technology's database reporting
>       language" <SQR-USERS@list.iex.net>
>
>
>To:   SQR-USERS@list.iex.net
>cc:
>Subject:  Date comparisons
>
>
>Hi,
>
>My application runs its report scripts using the "ngtsqr" utility which is
>a
>standard SQR engine re-linked with the application's specific libraries.
>The version of SQR which is uses is v3.0.7.0.1.  It is provided by the
>vendor of the application.  Therefore, I am not able to control which
>version I use.
>
>I have a sqr file which generates flat files.  To determine what data to
>place in one of the fields, I am using if statements.  Here is an extract
>of
>the code:
>
>begin-select
>SystemModificationDateTime      &SystemModificationDateTime
>SystemCreationDateTime          &SystemCreationDateTime
>TradeStatus
>         let #status = &TradeStatus
>         let #created = &SystemCreationDateTime
>         let #modified = &SystemModificationDateTime
>         if #status = 1 and &SystemCreationDateTime=
>&SystemModificationDateTime
>         print 'I' ()
>         else
>                 if #status = 1 and &SystemModificationDateTime >
>&SystemCreationDateTime
>                 print 'A' ()
>                 else
>                         if #status = 4
>                         print 'D' ()
>                         end-if
>                 end-if
>         end-if
>         print ',' ()
>OriginalTradeCode               (,,)
>...
>
>Basically, I am trying to determine the status of the trade i.e. insert
>(I),
>amend (A) or delete (D).  Where I have Insert and Deletions, the file is
>created with the appropriate flag i.e. I or D.  However for the amend case,
>I get no flag (no A).
>
>I don't think I can do a comparison of the dates where one is greater than
>the other.  However, I cannot use datediff either. I get the following
>error
>message so I guess that datediff was not available in this version:
>
>Error on line 39:
>    (SQR 4008) Unknown function or variable in expression: datediff
>let #diff = datediff(&SystemModificationDateTime, &SystemCreationDateTime,
>'second')
>
>Any suggestions on how I can compare the two dates to determine whether one
>is greater than the other.
>
>In this case, I could use where &SystemModificationDateTime !=
>&SystemCreationDateTime
>
>but further on in my code, I need to determine which date to use so I need
>to know which is the latest.
>
>I appreciate any help which you may be able to offer.
>
>Thanks
>
>Julie
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com