Code Snippet: How To Customize a DateTime with the FORMAT Function

Code Snippet: How To Customize a DateTime with the FORMAT Function

Warning: DOMDocument::loadHTML(): Tag template invalid in Entity, line: 12 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag svg invalid in Entity, line: 14 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag path invalid in Entity, line: 15 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag template invalid in Entity, line: 27 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag svg invalid in Entity, line: 29 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag path invalid in Entity, line: 30 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag template invalid in Entity, line: 12 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag svg invalid in Entity, line: 14 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag path invalid in Entity, line: 15 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag template invalid in Entity, line: 27 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag svg invalid in Entity, line: 29 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Warning: DOMDocument::loadHTML(): Tag path invalid in Entity, line: 30 in /home/customer/www/chrislagreca.com/public_html/wp-content/plugins/gistpress-master/includes/class-gistpress.php on line 473

Spaghetti For Dinner, Not For Code

If you’ve ever had to write SQL code to convert a datetime into a beautiful string like ‘20181222_082731’, then you would understand why I like the FORMAT function so much.

I use a date string similar to the example above to append the date to the end of certain filenames, e.g. backup files, log files, etc. Prior to SQL Server 2012, the code to do such a thing would be similar to the following:

DECLARE @TargetDate DATETIME = GETDATE();
DECLARE @DateFormat VARCHAR(15);
SET @DateFormat =
CONVERT(CHAR(4), YEAR(@TargetDate)) +
CASE WHEN MONTH(@TargetDate) < 10 THEN '0' + CONVERT(CHAR(1), MONTH(@TargetDate)) ELSE CONVERT(CHAR(2), MONTH(@TargetDate)) END +
CASE WHEN DAY(@TargetDate) < 10 THEN '0' + CONVERT(CHAR(1), DAY(@TargetDate)) ELSE CONVERT(CHAR(2), DAY(@TargetDate)) END +
'_' +
CASE WHEN DATEPART(HH, @TargetDate) < 10 THEN '0' + CONVERT(CHAR(1), DATEPART(HH, @TargetDate)) ELSE CONVERT(CHAR(2), DATEPART(HH, @TargetDate)) END +
CASE WHEN DATEPART(MI, @TargetDate) < 10 THEN '0' + CONVERT(CHAR(1), DATEPART(MI, @TargetDate)) ELSE CONVERT(CHAR(2), DATEPART(MI, @TargetDate)) END +
CASE WHEN DATEPART(SS, @TargetDate) < 10 THEN '0' + CONVERT(CHAR(1), DATEPART(SS, @TargetDate)) ELSE CONVERT(CHAR(2), DATEPART(SS, @TargetDate)) END
;
SELECT @DateFormat;
view raw datestring_old.sql hosted with ❤ by GitHub

SQL Server 2012 introduced a new function, FORMAT(), that allows a much cleaner way of doing the exact same thing. It’s also much friendlier to humans.

DECLARE @TargetDate DATETIME = GETDATE();
DECLARE @DateFormat VARCHAR(15);
SET @DateFormat = (SELECT FORMAT(@TargetDate, 'yyyymmdd_HHmmss')); -- HH is 24-hour format
SELECT @DateFormat;
view raw datestring_new.sql hosted with ❤ by GitHub

Over a larger set of data I would encourage you to test the performance of each approach and choose the most suitable to your needs.

Keep the spaghetti where it belongs — on the dinner table. If you find FORMAT useful or have other methods of parsing out date strings, please share in the comments.

Further Reading