SSIS expression: convert date to string

Question

I'm new to SSIS and I'm trying to convert a GetDate() to string "DD-MM-YYYY". This is the expression I've built so far:

(DT_WSTR, 8)  DAY( GETDATE()) + "-" + (DT_WSTR, 8)  (MONTH(GETDATE()) - 1) + "-" + (DT_WSTR, 8) YEAR(GETDATE())

The problem I've got is Month() converts the Month "23-4-2013" to a single character when I want it in Double character, same as day. How do i make it into a double character no matter what month it is?

Eleven

Answer 1

For SSIS you could go with:

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

Expression builder screen:

Otis

No problem, to subtract a month, change all the GETDATE() parts of the expression to DATEADD("mm",-1,GETDATE()) leaving everything else as is. – Otis

How can I make the expression so it's "mm-dd-yy" + ".CSV"? – Vendace

How to get datetime upto 3 fraction part ? – Confinement

If not using GETDATE() you may need to cast the variable to DT_DBDATE. Something like (DT_DBDATE)@[User::MY_DATE_VAR] – Counterpunch

If you want to have the expression so its "mm-dd-yyyyy" you can just switch the mm and dd like so: RIGHT("0" + (DT_STR,2,1252)DATEPART("mm",GETDATE()),2) + "-" + RIGHT("0" + (DT_STR,2,1252)DATEPART("dd",GETDATE()),2) + "-" + (DT_STR,4,1252)DATEPART("yy",GETDATE()) – Hydrokinetic

Answer 2

Something simpler than what @Milen proposed but it gives YYYY-MM-DD instead of the DD-MM-YYYY you wanted :

SUBSTRING((DT_STR,30, 1252) GETDATE(), 1, 10)

Expression builder screen:

Frustration

Answer 3

If, like me, you are trying to use GETDATE() within an expression and have the seemingly unreasonable requirement (SSIS/SSDT seems very much a work in progress to me, and not a polished offering) of wanting that date to get inserted into SQL Server as a valid date (type = datetime), then I found this expression to work:

@[User::someVar] = (DT_WSTR,4)YEAR(GETDATE()) + "-"  + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"  + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " " + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":" + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":" + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

I found this code snippet HERE

Salamanca

Answer 4

for the sake of completeness, you could use:

(DT_STR,8, 1252) (YEAR(GetDate()) * 10000 + MONTH(GetDate()) * 100 + DAY(GetDate()))

for YYYYMMDD or

RIGHT("000000" + (DT_STR,8, 1252) (DAY(GetDate()) * 1000000 + MONTH(GetDate()) * 10000 + YEAR(GetDate())), 8)

for DDMMYYYY (without hyphens). If you want / need the date as integer (e.g. for _key-columns in DWHs), just remove the DT_STR / RIGTH function and do just the math.

Alis

Answer 5

Try this. It will give you DD-MM-YYYY

SUBSTRING((DT_WSTR, 30)getdate(), 9,2)+"-"+SUBSTRING((DT_WSTR, 30)getdate(),6,2)+"-"+SUBSTRING((DT_WSTR,30)getdate(), 1,4)

Taps

Answer 6

This is the same as the most voted answer, just in another format:

@[User::path] ="MDS/Material/"+(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)

makes:

MDS/Material/2020/01/18

Saucepan

Please add some explanation to your answer by editing it such that others can learn from it – Emerick

This was not asked. – Cockrell