SQL Server - DateTime format - Asked By Mayank Tripathi on 05-Feb-13 04:43 AM

Hi Dear,

I am using Dateformat command, and other methods too to get the desired result, but could not get success.
Please  help in getting the desired result.

I am having a date-time column in a table (time is in 24 hours), I want to fetch the records and display this field in format

"03/14/2012 01:00:00 PM" ie "MM/dd/yyyy HH:mm:ss AM/PM".

DECLARE @Date DATETIME
SET @Date = GETUTCDATE()

SELECT @Date, CONVERT(VARCHAR, @Date, 101), CONVERT(VARCHAR(26), CAST(@Date AS Time), 109)

The result for above query is :
02/05/2013    9:36:06.2630000AM

Expected result is :
02/05/2013    09:36:06 AM

Thanks and Regards,
Mayank Tripathi
Mo. +91 730 484 7179

<img src="http://i.imgur.com/nOKSB5v.png">


Query

DECLARE @Date DATETIME
SET @Date = GETUTCDATE()
Declare @Time Varchar(12)
Set @Time= substring(convert(varchar(20), @Date , 9), 13, 8) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)
SELECT @Date, CONVERT(VARCHAR, @Date, 101), @Time



Output

2013-02-06 06:49:50.917    02/06/2013     6:49:50 PM