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:
[sourcecode language=”sql”]
/*

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;
[/sourcecode]