Re: Date formatting problem

[Joe Johnson <jejohn1216@SURFREE.COM>]

Glenn,

Check out the Datemath.sqc.

It contains a procedure called Convert-FROM-DTU-Date, which should fit your
needs (convert YYYY-MM-DD to native format).

Good Luck!

Joe Johnson
jejohn1216@surffree.com
-----Original Message-----
From: Ackerson, Glenn <Gsacker@IS.UNCO.EDU>
To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
Date: Friday, July 09, 1999 6:21 PM
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