Sometimes we need to generate current timestamp in a special format (e.g. suffixes for backup files). If we are unlucky that requested format is not supported by CONVERT function and SQL Server version is 2008 R2 and older this might be a little tricky.
In this blog post I will show how to get date with time in yyyymmddhhmiss (aka. YYYYMMDDHHmmSS or yyyyMMddHHmmss) in SQL Server versions up to 2008 R2 and 2012 and newer.
SQL Server up to 2008 R2
This is little tricky … REPLACE,REPLACE J
SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), CONVERT(DATETIME2(0), SYSDATETIME()), 120), '-', ''), ':', ''), ' ', '')
SQL Server 2012 and newer
This is more elegant way.
SELECT FORMAT(SYSDATETIME(), 'yyyyMMddHHmmss')
One more thing
Notice that I am using DATETIME2 type that was introduced in SQL Server 2008. DATETIME2 in default precise is more accurate than DATETIME, has larger range and has user-specified precise. Fe. if we need second precision we can use DATETIME2(0) which would require 6 bytes. DATETIME does not have user-specified precise so it would require 6 bytes.
Because of DATETIME2 I am using SYSDATETIME() function which returns DATETIME2 type.
SQL Mate Blog 