Date Format - SQL Server 2000

If date format is set by default language and the default language is set to
English and English has a mdy format, why would dates come out as ymd when
queried? Is it possible to set the date format on server level so that
whenever a user runs a query it comes out in mm/dd/yyyy format? I know I can
convert it to string then change the format but I don't want that option.
Please help..."Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...
SQL Server has no control over how dates are displayed in your client
application. You need to configure that setting client-side, either in the
Windows Control Panel or in the client application or your development
environment.
SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
control how strings get converted to dates. Those settings do not control
how dates get displayed by client applications.
You could convert your dates to strings and return those string values to
the client as a way of controlling the format. I think that's a bad idea
however. For one thing it means you can't maniplulate the values as proper
dates any more. For another, IMO it is inconsiderate to force the user to
live with the format that you prefer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's what I thought too. Just wanted to confirm. Thanks for your help.
"David Portas" wrote:

> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> SQL Server has no control over how dates are displayed in your client
> application. You need to configure that setting client-side, either in the
> Windows Control Panel or in the client application or your development
> environment.
> SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
> control how strings get converted to dates. Those settings do not control
> how dates get displayed by client applications.
> You could convert your dates to strings and return those string values to
> the client as a way of controlling the format. I think that's a bad idea
> however. For one thing it means you can't maniplulate the values as proper
> dates any more. For another, IMO it is inconsiderate to force the user to
> live with the format that you prefer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You can set the date format on a session level..
SET DATEFORMAT ydm
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...