Converting YYYYMMDD to MM/DD/YYYY
Posted on 2012-03-16
I have a SQL 2008 database with dates in a table stored in the format YYYYMMDD.
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:
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.