T-SQL Function to Return Current Timestamp as String

Sometimes, a DBA will have to make adhoc backups and it is ideal that these backup files are named with proper timestamp info. The below function returns current date and time as a string fomartted in ‘yyymmdd_hhmmss’ style:

/*

Script to create a function to get current timestamp in the format of YYYYmmdd_hhmmss; particularly useful for concatenating backup t-sql commands

SQL Version: 2005+

Last modified by Nick Xu on 2013-08-01

--Usage
SELECT DBADB.dbo.fn_GetTimeStamp()
GO
*/

--For generic setting
USE master
GO

IF OBJECT_ID('dbo.fn_GetTimeStamp') IS NOT NULL 
   DROP FUNCTION dbo.fn_GetTimeStamp 
GO 


CREATE FUNCTION dbo.fn_GetTimeStamp() 
RETURNS VARCHAR(15) 
AS 
BEGIN 

   RETURN CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')

END;
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in 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