SSIS Expression Builder - GetDate() To String (dd-MMM-yy)

user_8290345 0
Viewed 5785

I am trying to convert GETDATE() to a string in SSIS in the format of dd-MMM-yy (ex;, 03-NOV-17) as I am using Oracle as a data source.

I've developed the expression below, which will return 2017-11-03. This is close, but I need the "11" to read "NOV". 

(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())  + "-" + 
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)  + 
"-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

Any suggestions here?

etl ssis date expression sql-server
2 Answers

The SSIS expression builder doesn't support the DATENAME function, for example.

I would return this from an Execute SQL Task (mapping the result to a string variable), e.g.:

SELECT UPPER(FORMAT(GETDATE(), 'dd-MMM-yy')) Result

You could also set it in a Script Task, e.g.:

Dts.Variables["User::StringDate"].Value = DateTime.Now.ToString("dd-MMM-yy").ToUpper();
user_53145300

Problem

In SSIS expression the is no functions that can be used to change a date format

Solution

You can use the ? : conditional to achieve this. Microsoft Docs article

The following expression can be use to convert the date value returned by GETDATE() function to dd-MMM-yy format:

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)  + "-" +
(DATEPART("mm" , GETDATE()) == 1 ? "JAN" :
DATEPART("mm" , GETDATE()) ==  2 ? "FEB"  : 
DATEPART("mm" , GETDATE()) == 3 ? "MAR"  :
DATEPART("mm" , GETDATE()) == 4 ? "APR"   :
DATEPART("mm" , GETDATE()) == 5 ? "MAY" : 
DATEPART("mm" , GETDATE()) == 6 ? "JUN"  :
DATEPART("mm" , GETDATE()) == 7 ? "JUL"  :
DATEPART("mm" , GETDATE()) == 8 ? "AUG"  :
DATEPART("mm" , GETDATE()) == 9 ? "SEP"  : 
DATEPART("mm" , GETDATE()) == 10 ? "OCT"  : 
DATEPART("mm" , GETDATE()) == 11 ? "NOV"  : 
DATEPART("mm" , GETDATE()) == 12 ? "DEC" :"") 
+ "-" +  RIGHT( (DT_STR, 4, 1252) DATEPART("yy" , GETDATE()),2)

Other Solution

You can achieve this by adding a script task and writing a c# or Vb.net code to do this formatting

Dim FormatedDate as Date = Date.Now.ToString("dd-MMM-yy")
user_70312300
Related