date conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date conversion

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2009-04-30 : 16:30:50

Hi there,

How can I convert 2007-03-30 14:46:56.783 to 03/30/2007 14:46:56 PM
and similarly 2007-03-30 10:46:56.783 to 03/30/2007 10:46:56 AM ?

I know that best solution is to convert at the front end but for some reason I need to do it in sql.

Thanks for the help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 16:42:27

Here is one quick and dirty way:

DECLARE @MyDate DATETIME
SET @MyDate = CAST('2007-03-30 14:46:56.783' AS DATETIME)
SELECT CONVERT(VARCHAR(10), @MyDate, 101)
		+ ' ' + CONVERT(VARCHAR(12), @MyDate, 114)
		+ ' ' + RIGHT(CONVERT(VARCHAR(30), @MyDate, 100), 2)

ssunny
Posting Yak Master

133 Posts

Posted - 2009-04-30 : 16:46:56

Thanks for your reply Lamprey.
Using your query I get 03/30/2007 14:46:56:783 PM

but what I need is 03/30/2007 14:46:56 PM

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 16:53:02

quote:
Originally posted by ssunny

Thanks for your reply Lamprey.
Using your query I get 03/30/2007 14:46:56:783 PM

but what I need is 03/30/2007 14:46:56 PM

Thanks.

change it to..
DECLARE @MyDate DATETIME
SET @MyDate = CAST('2007-03-30 14:46:56.783' AS DATETIME)
SELECT CONVERT(VARCHAR(10), @MyDate, 101)
+ ' ' + CONVERT(VARCHAR(8), @MyDate, 114)
+ ' ' + RIGHT(CONVERT(VARCHAR(30), @MyDate, 100), 2)

ssunny
Posting Yak Master

133 Posts

Posted - 2009-04-30 : 16:58:24

yep got it thanks guys.

Raj_Mahendran
Starting Member

14 Posts

Posted - 2009-05-01 : 10:52:45

Shouldnt that be as 2:46:56 PM instead of 14:46:56 PM? When AM/PM gets in, the clock is only for 12hrs cycle. Makes sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources