Just the day formating in SSIS expression builder

0
votes

I'm trying to format(in yyyymmdd) the previous month's first day within the SSIS 2005 expression builder. So far I have managed to get the year and month correctly represented. However, I'm stuck when it comes to the day part of the expression.

Below is the complete errorneous code which I'm trying to hack.

RIGHT((DT_WSTR, 4) YEAR( DATEADD( "mm",-1, getdate() )), 4) +
RIGHT("0" + (DT_WSTR,2) MONTH( DATEADD( "mm",-1, getdate() )), 2)+
RIGHT("0" + (DT_WSTR,2) DAY(DATEADD("mm", DATEDIFF("mm", 0, GETDATE())-1,0)),2)

The first line returns the year ( in yyyy format) the second line returns the month (mm format). However, I'm stuck at returning the day part (in format 01 or just 1) in the third line.

Any help is appreciated.

2
datessisformat

2 Answers

1
votes

There's no need to do any fancy expression stuff for the day. The first day of the month is always 01 so your expression should be what you already had for year and month plus the concatenation of the literal string "01"

RIGHT((DT_WSTR, 4) YEAR( DATEADD( "mm",-1, getdate() )), 4) +
RIGHT("0" + (DT_WSTR,2) MONTH( DATEADD( "mm",-1, getdate() )), 2)+
"01"
0
votes

This will return a 2 char day part for any date:

right('0'+cast(day(getdate()) as Varchar(3)),2)