Kingston Dhasian (10/6/2010)
Normally formatting such as these is done in the front end. Anyways this should help you out
SELECT CONVERT(varchar(12), WDate, 103) + ' ' + CONVERT(varchar(28), WDate, 8) + ' ' +CASE WHEN DATEPART( HOUR, WDATE ) < 12 THEN 'AM' ELSE 'PM' END,
CONVERT(varchar(28), WDate, 9)
FROM Dateformat
That wouldn't work Kingston, any PM time will show up as a 24 hour clock but with PM at the end, which is redundant.
E.G.
DECLARE @Var AS SMALLDATETIME
SET @Var = '2010-06-18 17:33:17'
SELECT CONVERT(varchar(12), @Var, 103) + ' ' + CONVERT(varchar(28), @Var, 8) + ' ' +
CASE WHEN DATEPART( HOUR, @Var ) < 12 THEN 'AM' ELSE 'PM' END,
CONVERT(varchar(28), @Var, 9)
/*Returns
-------------------------------------------- ----------------------------
18/06/2010 17:33:00 PM Jun 18 2010 5:33:00:000PM
*/
Whereas: -
DECLARE @Var AS SMALLDATETIME
SET @Var = '2010-06-18 17:33:17'
SELECT CONVERT(VARCHAR, @Var, 103) + ' ' + Ltrim(
Substring(CONVERT(VARCHAR, @Var, 100), 13, Len(@Var))),
CONVERT(VARCHAR(28), @Var, 9)
/*Returns
------------------------------------------------------------- ----------------------------
18/06/2010 5:33PM Jun 18 2010 5:33:00:000PM
*/