Formatting dates for reporting

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Formatting functions

`CAST()`

`CONVERT()`

`FORMAT()`

The CAST() function

Supported since at least SQL Server 2000
Converts one data type to another, including date types
No control over formatting from dates to strings
ANSI SQL standard, meaning most relational and most non-relational databases have this function

Using the CAST() function

DECLARE
    @SomeDate DATETIME2(3) = '1991-06-04 08:00:09',
    @SomeString NVARCHAR(30) = '1991-06-04 08:00:09',
    @OldDateTime DATETIME = '1991-06-04 08:00:09';

SELECT
    CAST(@SomeDate AS NVARCHAR(30)) AS DateToString,
    CAST(@SomeString AS DATETIME2(3)) AS StringToDate,
    CAST(@OldDateTime AS NVARCHAR(30)) AS OldDateToString;

DateToString	StringToDate	OldDateToString
1991-06-04 08:00:09.000	1991-06-04 08:00:09.000	Jun 4 1991 8:00AM

The CONVERT() function

Supported going back at least to SQL Server 2000
Useful for converting one data type to another data type, including date types
Some control over formatting from dates to strings using the style parameter
Specific to T-SQL

Using the CONVERT() function

DECLARE
    @SomeDate DATETIME2(3) = '1793-02-21 11:13:19.033';

SELECT
    CONVERT(NVARCHAR(30), @SomeDate, 0) AS DefaultForm,
    CONVERT(NVARCHAR(30), @SomeDate, 1) AS US_mdy,
    CONVERT(NVARCHAR(30), @SomeDate, 101) AS US_mdyyyy,
    CONVERT(NVARCHAR(30), @SomeDate, 120) AS ODBC_sec;
GO

DefaultForm	US_mdy	US_mdyyyy	ODBC_sec
Feb 21 1793 11:13 AM	02/21/93	02/21/1793	1793-02-21 11:13:19

Sample CONVERT() styles

Style Code

1 / 101
3 / 103
4 / 104
11 / 111
12 / 112
20 / 120
126
127

Format

United States m/d/y
British/French d/m/y
German d.m.y
Japanese y/m/d
ISO standard yyyymmdd
ODBC standard (121 for ms)
ISO8601 yyyy-mm-dd hh:mi:ss.mmm
yyyy-mm-ddThh:mi:ss.mmmZ

The FORMAT() function

Supported as of SQL Server 2012
Useful for formatting a date or number in a particular way for reporting
Much more flexibility over formatting from dates to strings than either CAST() or CONVERT()
Specific to T-SQL
Uses the .NET framework for conversion
Can be slower as you process more rows

Using the FORMAT() function

DECLARE
    @SomeDate DATETIME2(3) = '1793-02-21 11:13:19.033';

SELECT
    FORMAT(@SomeDate, 'd', 'en-US') AS US_d,
    FORMAT(@SomeDate, 'd', 'de-DE') AS DE_d,
    FORMAT(@SomeDate, 'D', 'de-DE') AS DE_D,
    FORMAT(@SomeDate, 'yyyy-MM-dd') AS yMd;

US_d	DE_d	DE_D	yMd
2/21/1793	21.02.1793	Donnerstag, 21. February 1793	1793-02-21

The FORMAT() function can be slower than CAST() or CONVERT() when dealing with larger data sets.

Let's practice!

Time Series Analysis in SQL Server