Tuesday, October 15, 2013

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 ] )

How to fix cardinality estimation anomalies [Video]

Use the link mentioned below to watch the presentation that I delivered for PASS DBA Virtual Chapter about Filtered Statistics.  http:...