How to format a datetime field?

Hi,
I have a datetime field SDate with a value '1/1/02'. I want to display it
as 01/01/2002 in a view. So I use this statement:
SELECT CONVERT(datetime, SDate,101) from Table1.
But it still display it as 1/1/02. The Help says 101 will display yyyy if I
use it with CONVERT.
Thanks.declare @.t datetime
set @.t=getdate()
select convert(char(10),@.t,101)
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
> Hi,
> I have a datetime field SDate with a value '1/1/02'. I want to display it
> as 01/01/2002 in a view. So I use this statement:
> SELECT CONVERT(datetime, SDate,101) from Table1.
> But it still display it as 1/1/02. The Help says 101 will display yyyy if
> I use it with CONVERT.
> Thanks.
>
>|||Thanks a lot. I replaced datetime with char(10) and it works.
"Farmer" <someone@.somewhere.com> wrote in message
news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
> declare @.t datetime
> set @.t=getdate()
> select convert(char(10),@.t,101)
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
>|||Note of course that when you format it this way that it is no longer a date
value, it is a character value. May not be a problem for you, but it could
be confusing in how it gets used by a client program. and if you want to
sort by it, since it will sort on month first (assuming you are American!)
then day, then year.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot. I replaced datetime with char(10) and it works.
> "Farmer" <someone@.somewhere.com> wrote in message
> news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
>|||I think the best way, on the backend and on the client application is to
store the value in ISO format, so you need not mess up with cutting the
time, formatting from one pattern to another.
Just my two pence and experience within projects.
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> schrieb im Newsbeitrag
news:OihPLOzUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Note of course that when you format it this way that it is no longer a
> date value, it is a character value. May not be a problem for you, but it
> could be confusing in how it gets used by a client program. and if you
> want to sort by it, since it will sort on month first (assuming you are
> American!) then day, then year.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
>