The FORMAT function

Recently one of colleagues asked, how to format a date value according to the style he wants. His date style is as below;

10/15/13 - 04:11 pm

The immediate solution that came into my mind was the CONVERT function which you can specify the conversion style. He had already worked out a solution which using that function and the code is as below;

SELECT LEFT(Lower(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 1) + ' - ' + RIGHT( 
       CONVERT ( 
       VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17) 
       + ' ' 
       + Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS 
       "Date/Time" 

However it is bit of complex string concatenation. Wouldn’t there be any simple way of doing it? So after bit of research I found the following code which does the exact same thing that my colleague wants.

DECLARE @d DATETIME = CURRENT_TIMESTAMP; 

SELECT Lower(Format(@d, 'MM/dd/yy - hh:mm tt')) AS 'Date/Time' 

Using FORMAT function, you can format numeric and date values. It also supports the culture. The syntax is as below;

FORMAT ( value, format [, culture ] )