last day of last month date expression in ssis

Question

I have to create a derived column to upload a date in OLEDB destination because my source file doesn't contain this date. The date i want to get through derived column is last day of last month. Does anyone know how to get it?

Answer 1

Try the following expression, just substract the current day from the current date using DATEADD Function.

DATEADD("d", -DAY(GETDATE()), GETDATE())

If you want to remove time you have two choices:

1. convert to string

   LEFT((DT_STR,50,1252)DATEADD("d", -DAY(GETDATE()),GETDATE()),10)
   

2. convert to string then to date (it will generate time 12:00 AM)

   (DT_DATE)LEFT((DT_STR,50,1252)DATEADD("d", -DAY(GETDATE()),GETDATE()),10)
   

Answer 2

The simplest way if you don't need time is :

(DT_DBDATE)(DATEADD("d",-DAY(GETDATE()),GETDATE()))