Date Conversion

[Sean]

I need the code to convert a date from the DD/MM/YY format to a JD Edwards
Julian date format on the 400. The JDE date is on the format:
100001 for Jan 1, 2000
100365 for Dec 31, 2000
101031 for Jan 31, 2001
Well, you get the point.

Any help would be appreciated.

[Steve Swett]

I'm not sure which type of language you need the solution coded in. The
CVTDAT CL program on the AS/400 will do what you're looking for. You can
call a CL program from within an SQL statement by using SQL's "call"
command. (You can't call AS/400 commands, though, -- only programs.
Therefore, you'd have to call QSYS.QCMDEXC which accepts a CL command and
its parameters as parameters.)

Here is an example of a call used for another purpose (overriding database
file members):
----------------------------------------------------------------------------
----
IBM AS/400 Support Line Technical Document
__________________________________________________________________
Document Information
__________________________________________________________________

Document Title:
Accessing Multiple Member Files Using ODBC

Document Description:

SQL has no object type similar to an AS/400 multiple-member physical file.
An SQL statement run through Client Access ODBC or Client Access OLE DB
provider will always access the first member of a multi-member file. There
are two methods that can be used to access specific members. 1 Stored
Procedure Call to OVRDBF

AS/400 commands can be executed in ODBC by calling the execute command
system API. Use this technique to run a OVRDBF command to override to the
proper member before running the SQL statement. For example:

CALL QSYS.QCMDEXC('OVRDBF FILE(USER1) TOFILE(MYLIB/USER1) MBR(COMPANY)
OVRSCOPE(*JOB)', 0000000066.00000)
where 0000000066.00000 is a fixed decimal field with a length of 10 and 5
decimal places. The value 66 in the sample above must be the length of the
string (the number of characters including spaces between the single
quotes).

Note: The number must be zero filled.

An example of this is included in the Visual Basic ODBC sample program
available at the following URL:

ftp://testcase.software.ibm.com/as400/fromibm/ApiSamples/
2 SQL Alias

OS/400 R430 and later support an SQL alias statement. Create an alias for
each member that you need to access and then access the alias from the ODBC
application. The alias is persistent -- you only need to create it one time.
Any SQL tool (OS/400 interactive SQL, the ODBC sample program mentioned
above, and so on) can be used to create the alias.

For example:

CREATE ALIAS MYLIB.FILE1MBR1 FOR MYLIB.MYFILE(MBR1)
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)

The PC application would then specify MYLIB.FILE1MBR1 or MYLIB.FILE1MBR2
depending on which member it wants to access.
Considerations when using an alias with ODBC: o An alias is returned by the
ODBC catalog function as type "other". Some applications may not display
this type of file in their list of available tables.
o An alias can exist even if the file, member, and/or library it is an alias
for does not exist (similar to a symbolic link).
o There are several restrictions on SQL statements that can be used against
an SQL alias name that references a member. See DB2 for AS/400 SQL Reference
(SC41-5612) for further information.

__________________________________________________________________
PMR Number:
Related APARs:
Related Public Documents:

IBM disclaims all warranties, whether express or implied, including, but not
limited to, the implied warranties of merchantability and fitness for a
particular purpose. By furnishing this document, IBM grants no licenses to
any related patents or copyrights. Copyright (c) 1996,1997,1998, 1999 IBM
Corporation. Any trademarks and product or brand names referenced in this
document are the property of their respective owners. Consult your product
manuals for

-------------------------------------------------------------

The process of calling what is in effect a stored procedure for each date
would be too slow, I'd think. You may want to re-write a routine in your
desired language. I can provide you with an RPG program that converts
MM/DD/YY to Julian date so you can see the logic. Let me know if you're
interested.

Sean wrote in message <397c871b_1@dnews>...