I'm coping with dates from the AS/400 which are a kind of julian date. The month of january first 2000 returns like a string worth of "1". When the date were in true julian form it might seem like 2000001. The date 12/31/2049 is returns in the AS/400 as "49365". It is possible to method to format these dates during my C# code to appear like standard short dates?
Exactly what does The month of january 1, 2001 seem like?
Whether it appears like "1001", you are able to pad around the playing zeros to five numbers after which extract the two-digit year because the first couple of numbers and also the day-of-year number because the last 3. It will then be rather simple to transform your day-of-year number to some month and day if little else it can be done with a lot of
if claims on day ranges.
Whether it appears like "11" because you will find no leading zeros within the day number, you are just at a complete loss as there's not a way to distinguish between many dates, for example The month of january 1, 2001 and The month of january 11, 2000.
IF your dates will always be from the format 'yyddd':
If you're able to write your SQL statement directly, the next works...
SELECT CAST('20' || julianDate as date) FROM table
If you do not, consider writing a view that includes that behavior (that's a primary reason sights exist...).
For apparent reasons, all dates is going to be considered 2000 and then...
IF (for reasons uknown) it's getting rid of any leading zeros in every part (as stated in comments for @Anomie's answer), you're indeed simply toast. Frankly, the entire dataset is most likely a loss of revenue, as I am unsure how even RPG would have the ability to differentiate between certain dates correctly at that time.
IBM defines *JUL date format as yy/ddd. It's not generally used, but it's an available standard format supported around the AS/400. You if you have a string, therefore the assumption here is it is saved as CHAR(5), or 5A in DDS.
Should you column is known as jdt, obtain the right quantity of numbers inside your string, in SQL, with:
RIGHT(('00000' || TRIM(jdt)),5)
Now place the slash in:
INSERT( RIGHT(('00000'||TRIM(jdt)),5) ,3,0,'/')
DB2/400 can cast this to some real date area, however it is only going to work correctly if you're able to
SET OPTION DATFMT=*JUL. How to get this done from C# on Home windows would rely on the way you are hooking up.
Let us suppose you cannot find the best way to set the date format in your connection.
Solution: Produce a user defined function [UDF] in DB2.
First, choose a suitable library to keep the function in, and hang that as the current library. In OS/400 CL,
CHGCURLIB yourlib, or perhaps in SQL,
SET SCHEMA = yourlib. Thus automatically whatever you create goes into this library.
I suggest storing the SQL definition for the UDF inside a source member. (Request if unfamiliar) You are able to execute the origin using the
Your function definition could look something similar to this:
CREATE FUNCTION CvtJul2Date( jdtin char(5) ) RETURNS DATE LANGUAGE SQL SPECIFIC CVTJUL2DT DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURNS NULL ON NULL INPUT SET OPTION DATFMT = *JUL BEGIN RETURN( date( insert( right(('00000'||trim(jdtin)),5) ,3,0,'/') ) ); END
The *JUL choice is scoped towards the UDF. Any SQL query that works on the AS/400 should have the ability to do that conversion, no matter the DATFMT from the job (presuming you've put this function inside a library that is on that job's library list).