Converting YYYYMMDD to MM/DD/YYYY

Hi,

I have a SQL 2008 database with dates in a table stored in the format YYYYMMDD.

20041201
20040308
20040108
20040113

The above are examples of the dates in the tables.  The field is TRANDATE and the data is stored as decimal(9, 0).  I have written a SQL view to join a few tables so that we can use the view to pull data into microsoft excel with user friendly field names for analysis.  I am looking for a way to convert the dates so that they display as MM/DD/YYYY instead of the YYYYMMDD format.  I have searched ee and found the example:

Convert(VARCHAR(50),Convert(Datetime, '20110125'),101)

This successfully converts the string “20110125” to 01/25/2011.  I tried changing this to Convert(VARCHAR(50),Convert(Datetime, dbo.OESHDT.TRANDATE),101), however, I get the error message:

SQL Execution Error.

Executed SQL Statement: SELECT ……………………….
Error Source: .Net Sql Client Data Provider
Error Message: Arithmetic Overflow error converting expression to data type datetime.

I am extremely confused as to why I would get this error.  If I change the date string in the initial statement from '20110125' to any date like ‘20120316’, or with the function GetDate(), the date will be displayed correctly.  I would assume that putting the field name from the table would return the value and would work correctly.

Am I looking at this the wrong way?  Can someone please assist or show me another way?

Thanks in advance.
3GKIDAsked:
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.

HainKurtSr. System AnalystCommented:
select CONVERT(varchar(12),getdate(),101)

03/16/2012
0
HainKurtSr. System AnalystCommented:
select CONVERT(varchar(12),CONVERT(date, '20110125',100),101)

01/25/2011
0
lcohanDatabase AnalystCommented:
What is the datatype for dbo.OESHDT.TRANDATE column and more important what junk data you may have in it? My guess is that this is the reason for your convert to fail. Strings or int in that column that would represent dates outside the 1900 to 2049 SQL/Windos platform knows.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

HainKurtSr. System AnalystCommented:
and make sure you have valid data in the column for all rows...
0
effesCommented:
The problem is with this part of your statement:
Convert(Datetime, dbo.OESHDT.TRANDATE)

Open in new window

This tries to convert the number 20110125 and not the string '20110125' to datetime. That is not the same. You have to convert the value in TRANDATE to a string and then convert the result of that to datetime:
Convert(VARCHAR(50),Convert(Datetime, Convert(nvarchar, dbo.OESHDT.TRANDATE)),101)

Open in new window

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
HainKurtSr. System AnalystCommented:
to find invalid data:

SET LANGUAGE us_english;
SET DATEFORMAT ymd;

SELECT * from yourtable
WHERE ISDATE(TRANDATE) = -1
0
3GKIDAuthor Commented:
Hi.

Effes, you hit the nail on the head!  It took me quite a while to digest the statement you provided after trying it out.  Thanks a lot.

HainKurt, I tried the statement in the table and it produced no results.

Thanks a lot for all your 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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.