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