zlika's

My ex-colleague asked me how to insert query result in a variable for generate report in Microsoft SQL Server. After few explanations I noticed that she has decided to use SSIS (SQL Server Integration Services) to automate. She sent me an example in which she uses YEAR(), MONTH() and DAY() functions.

-- YEAR(), MONTH(), DAY()
SELECT
	'Test_'
	+ CAST(YEAR(GETDATE()) AS CHAR(4))
	+ '-'
	+ CAST(MONTH(GETDATE()) AS CHAR(2))
	+ '-'
	+ CAST(DAY(GETDATE()) AS CHAR(2))
	+ '.txt' AS DynamicFileName
GO

Result

The result is strange, because the return data type of those functions is integer. To fix this, we need to add leading zeros in front of days, months, hours, minutes and seconds.

-- Add leading zeroes
DECLARE
	@Day AS CHAR(2)
	, @Month AS CHAR(2)
	, @Year AS CHAR(4)
	, @Hour AS CHAR(2)
	, @Minute AS CHAR(2)
	, @Second AS CHAR (2)
	, @NewFileName AS CHAR(26)

SELECT @Day = DATEPART(DD, GETDATE())
SELECT @Day = RIGHT('0', 2 - LEN(@Day)) + @Day

SELECT @Month = DATEPART(MM, GETDATE())
SELECT @Month = RIGHT('0', 2 - LEN(@Month)) + @Month

SELECT @Year = DATEPART (YY, GETDATE())

SELECT @Hour = DATEPART(HH, GETDATE())
SELECT @Hour = RIGHT('0', 2 - LEN (@Hour)) + @Hour

SELECT @Minute = DATENAME(MI, GETDATE())
SELECT @Minute = RIGHT('0', 2 - LEN (@Minute)) + @Minute

SELECT @Second = DATEPART(SS, GETDATE())
SELECT @Second = RIGHT('0', 2 - LEN (@Second)) + @Second

SELECT @NewFileName = 'Test_' + @Year + '-' + @Month + '-' + @Day + '_' + @Hour + @Minute + @Second + '.txt'

SELECT @NewFileName AS DynamicFileName
GO

Result

The best way is to convert the result of GETDATE() function to CHAR and extract the different parts of it with the SUBSTRING() function.

-- SUBSTRING()
DECLARE
	@Date CHAR(20) = CONVERT(CHAR(10), GETDATE(), 112) -- yyyymmdd
	, @Time CHAR(20) = CONVERT(CHAR(10), GETDATE(), 108) -- hh:mi:ss

SELECT
	'Test_'
	+ SUBSTRING(@Date, 1, 4) -- Year
	+ '-'
	+ SUBSTRING(@Date, 5, 2) -- Month
	+ '-'
	+ SUBSTRING(@Date, 7, 2) -- Day
	+ '_'
	+ SUBSTRING(@Time, 1, 2) -- Hour
	+ SUBSTRING(@Time, 4, 2) -- Minute
	+ SUBSTRING(@Time, 7, 2) -- Second
	+ '.txt' AS DynamicFileName
GO

Result

My ex-colleague needs the dynamic filename to generate reports with SSIS. I’m gonna show her soon how to create simple project and the way for the same manipulation of the date in SSIS.

Félicitations