Re: Difference between date column variables and string variables - SOLUTION

[Tim Green <Tim_Green@NAD.ADP.COM>]

Thanks for the info - it gave me exactly what I needed.

My problem turned out to be that our Oracle date format is set to 'DD-Mon-YYYY',
but SQR's default format for Oracle - used when converting true dates to string
- is 'DD-Mon-YY'.  Therefore, once the date column variable was converted to
string, it failed to work as an argument.

A message in the archives mentioned I could set an environment variable,
SQR_DB_DATE_FORMAT, to 'DD-Mon-YYYY' to fix my problem, which indeed worked.
Better yet, after doing a little digging in the SQR 4 manual, I found that this
format can be set in the SQR.INI file.  Incredibly, the Unix versions of SQR
read default settings from SQR.INI, including all the default values for
settings that used to require the -M command line option.  I wonder how many
long time SQR users have failed to notice this new feature of version 4?

Thanks again,
Tim






Rosie ODonnell <joe.patton@NA.MARCONICOMMS.COM> on 06/11/99 08:45:09 AM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Tim Green/HWRD/ASG_Louisville/NAD)
Subject:  Re: Difference between date column variables and string variables




Tim,

The answer to question #1 is:  http://www.sqrug.com/sqr-users/

I haven't done what you are trying to do in question #2.

Joe Patton
Marconi Communications




Tim Green <Tim_Green@NAD.ADP.COM> on 06/11/99 08:27:04 AM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Joe Patton/LLP/RELTECCORP)

Subject:  Difference between date column variables and string variables




Two questions:

1.  Where is the list archive stored?  I believe I've seen this question
answered before, but I don't know where the archives are at to search them.

2.  I have a SQR program that fetches a date from the database into a column
variable.  If I use the column variable in another query directly, all is well.
However, if I pass the variable as a parameter to a procedure, which implicitely
converts it to a string, the query returns no results.  The problem is
illustrated in the example below:

Begin-Select
my_date        &my_date

     ! This works fine
     do Update_Another_Table

     ! This does not.
     do Update_Another_Table_Again( &my_date )

from my_table
End-Select


Begin-Procedure Update_Another_Table
     Begin-SQL
     Update another_table
             Set some_column = 'some_value'
      Where some_date = &my_date
     End-SQL
End-Procedure

Begin-Procedure Update_Another_Table_Again ( $my_date )
     Begin-SQL
     Update another_table
             Set some_column = 'some_value'
      Where some_date = $my_date
     End-SQL
End-Procedure

I believe the problem is related to the format used when converting the date to
a string.  When I display $my_date, it contains a date formated DD-Mon-YY,
without a century.  If I execute the update statement with the date hard-coded,
w/o the century, it finds no row to update;  with the century, it works fine.
So, if that is the problem, how do I get SQR to convert the date using a
DD-Mon-CCYY format?

Platform Information:
SQR: 4.2.3
DB:  Oracle 8.0.5
OS:  HP-UX 11.0

Thanks,
Tim