Joys With Datetimes

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

Joys with Dates

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.

Advertisements

About Jiří Hubáček

Experienced consultant and blogger for Microsoft SQL Server and related products. Likes cats, workouts and delicious food. Enjoys sailing, traveling, time spent with wife and friends.
This entry was posted in Microsoft SQL Server and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s