[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: Date comparisons
Good eye!
Tim
Mark Risman <mrisman@PAINEWEBBER.COM>@list.iex.net> on 03/14/2000 04:20:46
PM
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: Re: Date comparisons
Julie,
The mask "MM" is for month, not minute. Try using MM for month and MI for
minute.
- Mark
-----Original Message-----
From: Julie Lawton [mailto:lawton_julie@HOTMAIL.COM]
Sent: Tuesday, March 14, 2000 10:25 AM
To: SQR-USERS@list.iex.net
Subject: Re: Date comparisons
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