I want to convert DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE()) to YYYY-MM-DD format

Question

The expresssion

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

is correct and its giving me 2/1/2019 3:45:02 PM

But i want my answer to be as 2019-02-01 in SSIS

PLease help me

Answer 1

For some reason, SSIS is a bit of a pain when it comes to handling dates. Basically, you have to double cast it...

(DT_DATE)(DT_DBDATE)DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE())

Note: The evaluated value is 2/1/2019 12:00:00 AM. But, when you click OK on the Expression Builder, you will see the value displayed as 2/1/2019.

Edit To my knowledge, it is not possible to change the date format, even with converting it to string and then back to date. I wouldn't consider this a problem - leave the internal date format alone so that the back-end can handle it. The date format should only really be of any concern for display purposes, which can be manipulated during the SQL SELECT phase.

Is there a reason that you require the date format to be changed for back-end processing purposes?

Answer 2

If you are looking to get the value 2019-02-01 (format yyyy-MM-dd) then you should convert the value to a string and use the following expression:

LEFT((DT_WSTR,50)DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE()) ,10)

Output

2019-02-01

Based on the Cast (SSIS Expression) official documentation:

When a string is cast to a DT_DATE, or vice versa, the locale of the transformation is used. However, the date is in the ISO format of YYYY-MM-DD, regardless of whether the locale preference uses the ISO format.

If you need the returned value of type DT_Date then just add a CAST operation:

(DT_DATE)LEFT((DT_WSTR,50)DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE()) ,10)

Note that in Date data type there is no formatting, values are not stored in the same way they are visualized.

Output

2/1/2019 12:00:00 AM

Which is equivalent to

2/1/2019 00:00:00