Date formats from SQL Server to Ms Excel.
Posted by scmay on April 3, 2007
Have you ever generated an Excel report only to find that the dates have been formatted incorrectly? But not all of them are in incorrect order, only some dates are in incorrect format. Dates are normally converted to American time format (mm/dd/yyyy), but when it is unable to do so (month > 12 = error) then it leaves it as dd/mm/yyyy
Well, we manage to solve the error by reformatting the date cells once again, but we didn’t understand why Excel did that, and now the performance of the system is very slow because of reformatting cells.
Martin Green wrote an article about the same issue on dates and provides an unofficial understanding of what Excel is doing behind those codes. Apparently, Excel handles dates differently when you enter them directly on worksheets and when the dates come through the VBA codes. According to him, the best bet is to deliver the dates in ISO format, because Excel and VBA will correctly convert them as dd/mm/yyyy on the cells.
This article shows the different styles for date format in SQL Server. It is useful when your date in SQL Server is “smalldatetime”, but the dates in my server is currently all in varchar format (don’t ask me why…)
So in order to convert the current format dd/mm/yyyy to ISO format yyyy/mm/dd, I wrote this
CAST(DATEPART([Year], CONVERT(DateTime, BirthDate, 103)) AS varchar) + ”/” + CAST(DATEPART([Month], CONVERT(DateTime, BirthDate, 103)) AS varchar) + ”/” + CAST(DATEPART([Day], CONVERT(DateTime, BirthDate, 103)) AS varchar) AS BirthDate
(Style 103 is dd/mm/yyyy) Jeff Houser gives a better explanation on casting dates
