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 convert question

In TSQL, How could I convert the following string date time into string date
format of 'mm/dd/yyyy'
Aug 23 2005 9:27AM
TIA...I just came up with this one
select convert(varchar(20),cast('Aug 23 2005 9:27AM' as datetime),101)
Any other ideas'
"sqlster" wrote:

> In TSQL, How could I convert the following string date time into string da
te
> format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...|||sqlster wrote:
> In TSQL, How could I convert the following string date time into
> string date format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...
SQL Server dates do not have formatting attached to them. When you want
to convery a string representation of a date to a SQL Server DATETIME
data type, you should always use a portable format like YYYYMMDD.
If you need the output of the query to return in a USA date format, you
can use the CONVERT function with the correct style. Style 101 should
give you what you want. Although, I would encourage you to format dates
on the client whenever possible.
Select CONVERT(CHAR(10), CAST('20050823' as DATETIME), 101) -- with
century
Select CONVERT(CHAR(10), CAST('20050823' as DATETIME), 1) -- without
century
David Gugick
Quest Software
www.imceda.com
www.quest.com|||sqlster wrote:
> In TSQL, How could I convert the following string date time into
> string date format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...
If you need to use a time as well, use the following portable format:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
David Gugick
Quest Software
www.imceda.com
www.quest.com