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(
VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17)
+ ' '
+ Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS
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 ] )