I have a query that pulls data from my database.
The basic query looks like this:
SELECT ClosedDate, ServiceRequestID
FROM MyDatabaseNameHere
Now SQL runs this and all my dates are in the basic format.
(YYYY-MM-DD MM:SS:nnn)
Since this is not user friendly I modify my query with Convert.
SELECT CONVERT(VARCHAR(10), CloseDate, 101) AS [MM/DD/YYYY],
ServiceRequestID
FROM MyDatabaseNameHere
When ran this query now renders as MM/DD/YYYY
When I take this new query and put it into Excel (2010) the date renders as DD/MM/YYYY but the format comes in as "General" and Excel does not recognize this as Date data. However, if I click into the field and press the refresh button the data is converted to a date.
I backed up and ran my original query in Excel and wa-laa Excel made the date as a datetime. I found this a very frustrating user experience but its one of those things you run into in the real world and I thought it would be worthy of mentioning to your Excel users linking to SQL databases. Excel doesn't always play by SQL's rules.
Love the book and totally digging the YouTube vid's..
The basic query looks like this:
SELECT ClosedDate, ServiceRequestID
FROM MyDatabaseNameHere
Now SQL runs this and all my dates are in the basic format.
(YYYY-MM-DD MM:SS:nnn)
Since this is not user friendly I modify my query with Convert.
SELECT CONVERT(VARCHAR(10), CloseDate, 101) AS [MM/DD/YYYY],
ServiceRequestID
FROM MyDatabaseNameHere
When ran this query now renders as MM/DD/YYYY
When I take this new query and put it into Excel (2010) the date renders as DD/MM/YYYY but the format comes in as "General" and Excel does not recognize this as Date data. However, if I click into the field and press the refresh button the data is converted to a date.
I backed up and ran my original query in Excel and wa-laa Excel made the date as a datetime. I found this a very frustrating user experience but its one of those things you run into in the real world and I thought it would be worthy of mentioning to your Excel users linking to SQL databases. Excel doesn't always play by SQL's rules.
Love the book and totally digging the YouTube vid's..
