CODEWITHSUNDEEP
ssisexpressiongetdate
resolver101
resolver101

Reputation: 2255

SSIS expression: convert date to string

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?

Upvotes: 27

Views: 166408

Answers (6)

user12797113
user12797113

Reputation: 1

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

Upvotes: -1

user716255
user716255

Reputation: 453

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)

Upvotes: 2

Thomas Franz
Thomas Franz

Reputation: 319

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.

Upvotes: 1

Bob
Bob

Reputation: 477

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:

enter image description here

Upvotes: 24

Zach Smith
Zach Smith

Reputation: 8971

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

Upvotes: 5

Milen Kindekov
Milen Kindekov

Reputation: 1973

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:

Expression builder screen

Upvotes: 50

Related Questions