Casting as datetime -- sql server new users

Christoph
3/31/2005 7:46:08 AM

I can do the following

SELECT CAST( '20050331' as datetime)

and I will get back

3/31/2005

How can I format the string being cast to include the time as well?
I've tried

SELECT CAST( '20050331T115959' as datetime)
SELECT CAST( '20050331115959PM' as datetime)
SELECT CAST( '20050331235959' as datetime)

and permutations thereof but each just return a syntax error.
How can I format the string such that it returns

3/31/2005 11:59:59 PM

or

3/31/2005 23:59:59?

thnx,
Christoph

Mike Epprecht (SQL MVP)
3/31/2005 6:42:08 PM

SELECT CONVERT(NCHAR(25), CAST('2005-03-03 23:59:59' AS DATETIME), 100)

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/

[quoted text, click to view]

sql server new users : Casting as datetime