Date Issue!

I am storing dates in SQL Server 2000 (Using ASP).
The date is getting validated against the Ymd format, by using SET DATEFORMA
T.
In the Enterprise Manager - the date is displayed in local format (dmY).
When i run a store procedure manually it is returned as Ymd.
When i execute stored procedure from asp and load data into a recordset it i
s
displayed in mdY Format.
This is obviously quite confusing. Can anyone give me some insight into what
is happening. How i can retrieve a date in one format (Ymd).Hi
Seems u have to format date using FORMAT Function
renjith
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).|||I dont know which Function you mean with FORMAT, but here is another option
for that. A good pratice for me is to get the data back in a very common
format and to format it at the client side. The settings the date and time
is formatted depends on some settings concerning the client, the User
connnction to SQL Server (with its special user localized settings) or the
server settings. Perhaps you should read here a little bit further:
http://www.karaszi.com/SQLServer/in...p#OutputFormats
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
> Hi
> Seems u have to format date using FORMAT Function
> renjith
> "AJ" wrote:
>|||Hi
since AJ is using ASP , there is format function in VB for formatting data
so tht he can give as Format(DateColumn, "Ymd").
Renjith
"Jens Sü?meyer" wrote:

> I don′t know which Function you mean with FORMAT, but here is another opt
ion
> for that. A good pratice for me is to get the data back in a very common
> format and to format it at the client side. The settings the date and time
> is formatted depends on some settings concerning the client, the User
> connnction to SQL Server (with it′s special user localized settings) or t
he
> server settings. Perhaps you should read here a little bit further:
> http://www.karaszi.com/SQLServer/in...p#OutputFormats
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
>
>|||OK, that makes sense, i just thought you mean th non-existing function
FORMAT for TSQL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6F1D14DE-AB4F-4CE9-B7BE-6FDC5C1772EC@.microsoft.com...
> Hi
> since AJ is using ASP , there is format function in VB for formatting data
> so tht he can give as Format(DateColumn, "Ymd").
> Renjith
>
> "Jens Smeyer" wrote:
>|||The regional settings on your computer influence how the date is presented
in Enterprise Manager. So if the regional settings are British Enterprise
Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
influenced by the regional settings, and will always display dates in
yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
However, how SQL Server interprets the dateformat of strings depends on the
settings for your login in SQL Server. In Enterprise Manager, look under
<Server>\Security\Logins and look at the Default Language for your username
(or BUILTIN\Administrators if you are a Windows administrator on your local
machine).
To avoid problems with dates as strings, use the formats that are always
interpreted the same by SQL Server independent of any settings:
yyyymmdd and
yyyy-mm-ddThh:mm:ss
Jacco Schalkwijk
SQL Server MVP
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET
> DATEFORMAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
> is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into
> what
> is happening. How i can retrieve a date in one format (Ymd).|||> Query Analyzer isn't
> influenced by the regional settings
... unless you check Tools, Options, Connections, "Use regional settings wh
en displaying ... dates
and times". :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eZ5Z1z3XFHA.3040@.TK2MSFTNGP14.phx.gbl...
> The regional settings on your computer influence how the date is presented
> in Enterprise Manager. So if the regional settings are British Enterprise
> Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
> influenced by the regional settings, and will always display dates in
> yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
> However, how SQL Server interprets the dateformat of strings depends on th
e
> settings for your login in SQL Server. In Enterprise Manager, look under
> <Server>\Security\Logins and look at the Default Language for your usernam
e
> (or BUILTIN\Administrators if you are a Windows administrator on your loca
l
> machine).
> To avoid problems with dates as strings, use the formats that are always
> interpreted the same by SQL Server independent of any settings:
> yyyymmdd and
> yyyy-mm-ddThh:mm:ss
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AJ" <AJ@.discussions.microsoft.com> wrote in message
> news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>|||You need to change the query in all enviornments to use the convert date
standard formats.
select convert(nvarchar,getdate(),101)
Which returns in mm/dd/yyyy format.
05/23/2005
Refer to books online and search for CAST and CONVERT as search criteria and
you will find the list of output standards.
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).

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...
> > 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
> --
>
>|||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...

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...