SSIS Expression for first day of the current year and the last day of the most recent quarter

1
votes

I need to get the frist date of the year and the last day of the most recent quarter (eg "1-1-2019 to 9-30-2019" since today is 12/12.19) using SSIS expressions.

I can do this by using SQL statement like this below.

SELECT CONVERT(VARCHAR(10), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), 101) +' to  ' +  CONVERT(VARCHAR(10), DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)), 101) AS QuarterToDate

However, I cannot do this in SSIS Expression Builder. I'm using the following.

CONVERT(VARCHAR(10), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), 101) +' to  ' +  CONVERT(VARCHAR(10), DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)), 101)

But I get the following error message:

Expression cannot be evaluated.

The function "VARCHAR" was not recognized. Either the function name is incorrect or does not exist.

Attempt to parse the expression "CONVERT(VARCHAR(10), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), 101) +' to ' + CONVERT(VARCHAR(10), DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)), 101)" failed and returned error code 0xC004708A. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

enter image description here

Expression builder screenshot

2
sql-serverssisexpression

2 Answers

1
votes

Rewriting what you have above:

First date of the year:

DATEADD("year", DATEDIFF("year",(DT_DATE)2,getdate()), (DT_DATE)2)

Last day of the most recent quarter:

DATEADD("day", -1, DATEADD("quarter", DATEDIFF("quarter", (DT_DATE)2, GETDATE()), (DT_DATE)2))

Getting that into a different format requires some creativity.
You might need to stick it into a variable and work on the new variable via SUBSTRING().

Shortest Path: YYYY-MM-DD

LEFT((DT_WSTR, 30)DATEADD("day", -1, DATEADD("quarter", DATEDIFF("quarter", (DT_DATE)2, GETDATE()), (DT_DATE)2)),10)
0
votes

The first day of the year is easy. 

"01/01/" + (DT_WSTR,4) YEAR(GETDATE())

To get the last day of the most recent quarter, I stored it as it's own variable, called "EOQ" so it's easier to manipulate later. It is a string in YYYY-MM-DD format

(DT_WSTR,10) (DT_DBDATE) DATEADD("day", -1, DATEADD("quarter",DATEDIFF("quarter", (DT_DATE) 2, GETDATE()),(DT_DATE) 2))

Then we can combine the information to get your desired string (for a report title?)

"01/01/" + (DT_WSTR, 4) YEAR(GETDATE()) + " to " + SUBSTRING(@[User::EOQ],6,2) + "/" + SUBSTRING(@[User::EOQ],9,2) + "/" + SUBSTRING(@[User::EOQ],1,4)

Result: 01/01/2019 to 09/30/2019