SQL AS400 YYYYDDD convert to mm/dd/yyyy

[GeorgeC8]

Hi all,
I have searched a great deal for a solution but can't find my exactly what I need, or at least I can't put it all together.
First I need to retrieve a stored date from my AS400 and return it in a date format of mm/dd/yyyy or mm/dd/yy.
All date fields have a corresponding numeric 7,0 field and numeric 6,0 field. YYYYDDD and MDDYY respectively.

Example of a date field named NPTD7 would appear as 2011006 or 2/1/2011 as 2011032.
If it is simpler I could work with the other version, NPTD6 in this sample, so 2/1/2011 would appear as 20211 (note that if the month is only 1 digit, it does not include the zero but drops it which throws me a curve when I try and use something like:

date(SUBSTRING(CAST(NPDT6 AS VARCHAR(6)),1,2)concat'/'concat
SUBSTRING(CAST(NPDT6 AS VARCHAR(6)),3,2)concat'/'concat
SUBSTRING(CAST(NPDT6 AS VARCHAR(6)),5,2)) AS MyDate

What SQL can I use to convert either of these?

Second question is I then need to build a query to retrieve all records where NPTD7 are >= curdate() and return the NPTD7 as a mm/dd/yyyy or mm/dd/yy value.

Thanks kindly.

 

[gmmastros]

YYYYDDD is considered a Julian date. Converting that to an actual DateTime value is relatively straight forward.

Let's take the value 2011032 for example.

If we divide this value by 1000, we get 2011.032000.
If we use the mod operator:

Select 2011032 % 1000

We get 32.

So all we need to do is to use these 2 operators in conjunction with the date add function, like this.

Declare @Julian Numeric(7,0)
Set @Julian = 2011032

Select DateAdd(Year, @Julian / 1000 - 1900, @Julian % 1000-1)

We need to subtract 1900 from the division part because that is SQL server's 0 date. We need to subtract 1 from the day part because for Jan 1 we went to add 0 days, but in the Julian date value, it is represented at 001.

If you want to construct a Julian date from current date...

Select Year(GetDate()) * 1000 + DatePart(DayOfYear, GetDate())

To get your data...

Select DateAdd(Year, NPTD7  / 1000 - 1900, NPTD7  % 1000-1)
From   YourTable
Where  NPTD7 >= Year(GetDate()) * 1000 + DatePart(DayOfYear, GetDate())

Note that your data will return as a DateTime value. You can then format this date in your front end.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GeorgeC8]

Thanks for the reply.
I now wonder if I have posted my question in the correct forum as I really was needing the SQL select statement construct for converting from the julian to a "readable" date such as mm/dd/yyyy.

I apologize if I have incorrectly posted or did not grasp your post recommendations.
For further insight, I am writing a ASP page in Dreamweaver and I am at the data binding area trying to retrieve data from my AS400. I have just become stuck on the part where I present this date field in a useable fashion the the viewer - plus my #2 issue.

 

[GeorgeC8]

When I try just the last part of your post - DateAdd(Year, NPDT7 / 1000 - 1900, NPDT7 % 1000-1)
I get [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token % was not valid. Valid tokens: ),.

Could this be an incompatible token?

 

[GeorgeC8]

I have tested around and found this statement to work so far. Perhaps you can tell me if there may be a circumstance where this will yield an incorrect date such as the first or last date of a month?

DATE(DIGITS( DECIMAL(npdt7 +0.90000,7,0))) AS MyDATE

If there is any correlation to your explanation, I would be glad to know/learn.

George