25

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?

resolver101
  • 2,155
  • 11
  • 41
  • 53

6 Answers6

46

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

Milen Kindekov
  • 1,903
  • 1
  • 23
  • 31
  • 2
    No problem, to subtract a month, change all the `GETDATE()` parts of the expression to `DATEADD("mm",-1,GETDATE())` leaving everything else as is. – Milen Kindekov May 24 '13 at 09:39
  • How can I make the expression so it's "mm-dd-yy" + ".CSV"? – Si8 Apr 10 '14 at 12:56
  • How to get datetime upto 3 fraction part ? – Shesha Oct 22 '18 at 08:23
  • If not using `GETDATE()` you may need to cast the variable to `DT_DBDATE`. Something like `(DT_DBDATE)@[User::MY_DATE_VAR]` – MattR Mar 13 '19 at 17:34
  • 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()) – David Reke Sep 15 '21 at 13:46
23

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

Bob
  • 467
  • 1
  • 4
  • 13
4

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

Zach Smith
  • 8,458
  • 13
  • 59
  • 133
0

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.

Thomas Franz
  • 299
  • 1
  • 8
0
@[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)
4b0
  • 21,981
  • 30
  • 95
  • 142
user12797113
  • 1
0

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)
user716255
  • 352
  • 1
  • 6
  • 18