skcadavre (10/6/2010)

---

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

*/