Date format

Hi all,
I want to return the correct format
print CONVERT(CHAR(26),GETDATE(),22)
returns
09/28/05 12:25:18 PM
would like to return it like this 09/28/2005 12:25:18 PM
How please?
thanks
gvHi
22 is not a familiary style I recognised and it does not seem to be
documented in books online! Normally you would expect to add 100 to the styl
e
to get a four digit year, but you may want to try something like:
SELECT STUFF(CONVERT(CHAR(26),GETDATE(),22),7,2
,CAST(YEAR(GETDATE()) AS
CHAR(4)))
John
"gv" wrote:

> Hi all,
> I want to return the correct format
> print CONVERT(CHAR(26),GETDATE(),22)
> returns
> 09/28/05 12:25:18 PM
> would like to return it like this 09/28/2005 12:25:18 PM
> How please?
> thanks
> gv
>
>|||CONVERT(CHAR(26),GETDATE(),122)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"gv" <viatorg@.musc.edu> wrote in message
news:O%23J73pExFHA.3856@.tk2msftngp13.phx.gbl...
> Hi all,
> I want to return the correct format
> print CONVERT(CHAR(26),GETDATE(),22)
> returns
> 09/28/05 12:25:18 PM
> would like to return it like this 09/28/2005 12:25:18 PM
> How please?
> thanks
> gv
>|||Using 122 I get error
Server: Msg 281, Level 16, State 1, Line 3
122 is not a valid style number when converting from datetime to a character
string.
Server: Msg 8114, Level 16, State 1, Line 3
Error converting data type datetime to varchar.
thanks
gv
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OdAPe2ExFHA.3000@.TK2MSFTNGP12.phx.gbl...
> CONVERT(CHAR(26),GETDATE(),122)
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "gv" <viatorg@.musc.edu> wrote in message
> news:O%23J73pExFHA.3856@.tk2msftngp13.phx.gbl...
>|||thanks
gv
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:29AE5D05-1362-46AC-9A48-230CBF9BA4B3@.microsoft.com...
> Hi
> 22 is not a familiary style I recognised and it does not seem to be
> documented in books online! Normally you would expect to add 100 to the
> style
> to get a four digit year, but you may want to try something like:
> SELECT STUFF(CONVERT(CHAR(26),GETDATE(),22),7,2
,CAST(YEAR(GETDATE()) AS
> CHAR(4)))
> John
> "gv" wrote:
>