Re: Date formatting problem

[Reinier de Ruiter <Reinier.DeRuiter@APOLLOGRP.EDU>]

Glenn,

If you use SQR4 you should use the date type variable and not a string type
variable. This way you won't have any problems with formats.

If you are using SQR3 you are probably using different formats. Try to use
the to_char and to_date functions from oracle to be sure what format the
strings are.

good luck,

Reinier de Ruiter
Programmer/Analyst
Apollo Group, University of Phoenix
Phoenix, Arizona
tel:480-557-1158
email work: reinier.deruiter@apollogrp.edu
email home: rruiter@yahoo.com



-----Original Message-----
From: Ackerson, Glenn [mailto:Gsacker@IS.UNCO.EDU]
Sent: Friday, July 09, 1999 4:02 PM
To: Multiple recipients of list SQR-USERS
Subject: Date formatting problem


Hello all,
  It's Friday afternoon, and I must be having mental gridlock on this
problem, so please forgive what must sound like a VERY
elementary question regarding date formatting. I'm creating a
customized version of Peoplesoft's paysheet update SQR
(PAYUPDT,) retrieving a date (Pay_End_Dt), and having a problem
getting that retrieved date formatted correctly for use in a
subsequent Select statement WHERE clause. I could swear I've
dealt with this successfully before, but for some reason, I can't
seem to hit the mark on this today. Here's the scenario:

(1) I get the PAY_END_DT from the pay calendar table
(2) I move that retrieved date into a variable $Pay_End_Dt - the
date displays with a value such as "1999-06-14"
(3) I then try to get the Page# and Line# from the PAY_PAGE
table, using the $Pay_End_Dt variable in the WHERE clause.

Program execution using the $Pay_End_Dt variable causes the
error:

(SQR 5528) ORACLE OEXEC error -1843 in cursor 8:
   ORA-01843: not a valid month

  I'm assuming that the WHERE clause that's getting an error is
expecting the date in native format, namely "dd-mon-yy" (we're
using Oracle.) So, I've tried using the Peoplesoft "datetime.sqc" in
a variety of formats to convert the date from "yyyy-mm-dd" to native
format "dd-mon-yy" and every try has failed.
  Can anyone provide some assistance on this one? I would be
most appreciative. Since I get the SQR listserve messages in
digest format, if you wouldn't mind either cc'ing the message to me
directly or sending a reply to me directly, that would be great.
Thanks for your help!

Cheers,
Glenn Ackerson

**************************************************
Glenn Ackerson
Information Technology Professional III
Internet Address: gsacker@is.unco.edu
University of Northern Colorado
Information Services
Carter Hall   Greeley, CO  80639