Datetime convert problem

i am using select CONVERT(varchar(15),getdate(),101)
result is 1/23/2006 but i want result in 01/23/2005
what am i doing wrong. Any db settings?I'd suggest you use the ISO standard date 112 instead, eg:-
select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
Here's why :-
SET language english
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
SET language french
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
>|||But i need date in MM/dd/yyyy format.
cant use 112 which is yyyyMMdd
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
> I'd suggest you use the ISO standard date 112 instead, eg:-
> select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
> Here's why :-
> SET language english
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> SET language french
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>|||In that case this may be of use to you :-
http://www.aspfaq.com/show.asp?id=2460
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
> But i need date in MM/dd/yyyy format.
> cant use 112 which is yyyyMMdd
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
>|||"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
It works on sql server 2000. Perhaps this is a problem with the tool you
are using to view the results.|||no use
<snip>
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @.dt, 101)
</snip>
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oh0EueDIGHA.216@.TK2MSFTNGP15.phx.gbl...
> In that case this may be of use to you :-
> http://www.aspfaq.com/show.asp?id=2460
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
>|||it works fine on query analyzer
but it doesnot work when it is placed in a trigger.
trigger files it in some db table :'?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23SfFqgDIGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> It works on sql server 2000. Perhaps this is a problem with the tool you
> are using to view the results.
>|||> it works fine on query analyzer
> but it doesnot work when it is placed in a trigger.
> trigger files it in some db table :'?
That wasn't the question you asked. The responses you have received assume
that you are having problems with the output formats - that, indeed, is not
the problem at all. Perhaps you need to read the following:
http://www.aspfaq.com/show.asp?id=2023
Tibor has some information that explains this in a format that is more
user-friendly, but it appears to be down. Have a look at it (with respect
to datetime values) when it comes back up.
http://www.karaszi.com/sqlserver/articles.asp
If that does not clear up the problem, then define "doesn't work".