We help IT Professionals succeed at work.

Date Display in SQL Server 2008

TomCoon
TomCoon asked
on
Although my regional setting on my SQL Server are set to a customized date format of mm/dd/yyyy when I open a table it displays date yyyy-mm-dd and when my application retrieves information from the table that is the format it comes across in (yyyy-mm-dd).  How can it get it back to displaying as mm/dd/yyyy so I don't have for format every call to a saved date field.
Comment
Watch Question

Use Covert-101 in your queries to ensure you always get the dates in mm/dd/yyyy format, e.g.
SELECT CONVERT(VarChar(50), GETDATE(), 101)
produces: 06/10/2010

Author

Commented:
The queries are in the application, so I can't get to them to do a convert or cast.
If they queries are in the app, then there's a good chance that you don't have anything to worry about, because no decent developer would perform and unnecessary conversaion from Date to string and back when working with Date fields, instead they'd use DateTime which is a numeric field supported by all development platforms/tool. What you see when you open the table in Management Studio, a report, a data grid is just a character representation of the actual values; inside the database all date/time values are stored in numeric format.
Are you actually getting any exception or error from the apps?
Top Expert 2012
Commented:
How dates are displayed are strictly a function of the client software you are using and SSMS for one does not use your regional settings for this purpose.

Author

Commented:
From http://msdn.microsoft.com/en-us/library/ms174173.aspx 
"SQL Server Management Studio presents dates formatted with the Microsoft Windows Regional and Language Options in effect when SQL Server Management Studio was started. Restart SQL Server Management Studio to reflect newer settings."

But SSMS is not using the regional settings to display the dates, it's using the yyyy-mm-dd format.
Top Expert 2012

Commented:
>>But SSMS is not using the regional settings to display the dates, it's using the yyyy-mm-dd format.<<
As I stated: "SSMS for one does not use your regional settings for this purpose."
Top Expert 2012
Commented:
Here is the bottom line:
Data formatting is the responsibility of the presentation layer.  If you are unable or unwilling to modify the presentation layer, than you will have to modify each and every query to convert the datetime value to a varchar value with the custom formatting you want to see and for this you use the CONVERT function.

Author

Commented:
I've requested that this question be deleted for the following reason:

No anwser was provided
Top Expert 2012

Commented:
The author did not like the answer I gave them.
TomCoon,
 
I am starting the self-close process on this question because it appears to me that the Expert(s) answered your question, even if it was not necessarily the answer you wanted to see.
 
modus_operandi
EE Admin