Neat but I also use old style CONVERT(). seems simpler and probably more efficient. Just supply the proper [style] code.

/*

http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.105%29.aspx

100mon dd yyyy hh:miAM (or PM)

101mm/dd/yyyy

102yy.mm.dd

103dd/mm/yyyy

104dd.mm.yy

105dd-mm-yy

106dd mon yy

107Mon dd, yy

108hh:mi:ss

109mon dd yyyy hh:mi:ss:mmmAM (or PM)

110mm-dd-yy

111yy/mm/dd

112yymmdd

113dd mon yyyy hh:mi:ss:mmm(24h)

114hh:mi:ss:mmm(24h)

120yyyy-mm-dd hh:mi:ss(24h)

121yyyy-mm-dd hh:mi:ss.mmm(24h)

126yyyy-mm-ddThh:mi:ss.mmm (no spaces)

127yyyy-mm-ddThh:mi:ss.mmmZ

130dd mon yyyy hh:mi:ss:mmmAM

131dd/mm/yy hh:mi:ss:mmmAM

*/

-- CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT CONVERT(VARCHAR(24),GETDATE(),121)