AS400 date is YYYMMDD in table, need MM/DD/YYYY from SQL

I have a date in an AS400 table in format YYYYMMDD (numeric), I need to run a sql statement and get it out in the format MM/DD/YYYY, USA rather than ISO.  Here is my sql I found while googling.  I'm wanting to pull it out nice and pretty in a date format for .NET.  Thanks

SELECT DATE(timestamp_format(CHAR(WNSDATE), 'YYYYMMDD')) AS myDate FROM AIR400.WEBNOTE
LVL 2
bergertimeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
I've always found it mildly strange that we can use the SUBSTR function on an integer, but we can use it to our advantage.

e.g.
select anInteger,                    
       char(date(                    
       substr(anInteger,1,4) || '-'||
       substr(anInteger,5,2) || '-'||
       substr(anInteger,7,2)), USA)  
  from deleteme

 ANINTEGER   CHAR conversion
20,131,011     10/11/2013   
20,131,012     10/12/2013   

Open in new window


I believe that's what your looking for.

HTH,
DaveSlash
0
Dave FordSoftware Developer / Database AdministratorCommented:
This'll do it, too:

select anInteger,
       char(date(to_date(varchar(anInteger), 'YYYYMMDD')), usa)
  from deleteme

 ANINTEGER   CHAR conversion
20,131,011     10/11/2013
20,131,012     10/12/2013

Open in new window


HTH,
DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
What data type do you want to end up with?  

Your original query looks fine to me, but it delivers a DATE field, and how a date field is rendered on screen is a function of the date format setting for the connection.

If you want to force it into character format, for example, you can do this:

SELECT CHAR(DATE(timestamp_format(CHAR(WNSDATE), 'YYYYMMDD'),USA) AS usaDate FROM AIR400.WEBNOTE

Or, depending on the data connector you are using, you can set your connection string to transform dates on the fly into USA format.  What data connector are you using?
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

bergertimeAuthor Commented:
So char(date(to_date(varchar(anInteger), 'YYYYMMDD')), usa) gets the format right, but I need it left in a DATE format.  I'm using a Grid in ASP.NET, using the client access data adaptor.  Normally I substring it out to make it nice, but I'm trying to use the form view and load the calendar controls with the date without code behind.  I can do it with my line above:

DATE(timestamp_format(CHAR(WNSDATE), 'YYYYMMDD')) AS myDate

But my users will whine about it being 2013-10-11 format ISO and want it in USA 10/11/2013.

Go figure....thanks for the help.
0
Gary PattersonVP Technology / Senior Consultant Commented:
Yeah, dealt with the "user issue" before.  How hard is it to just read an ISO date, really?  But they will.

Any reason you can't just set the format you want to see for date fields on the Grid object?

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
0
bergertimeAuthor Commented:
Thanks, you guys are always a great help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.