SSIS Lookup Expression for Formatting a Date in Dynamic SQL

user_6590812 0
Viewed 334

I have a variable in my SSIS package.

@LastFridayDateTime = Datetime Datatype = DATEADD("hh",17,(DT_DBTIMESTAMP) (   (DT_STR,30,1252) (DT_DBDATE) (DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()) ) ) )

This gives me a value = 6/14/2019 5:00:00 PM

Now I am trying to use dynamic Select query for my SSIS lookup expression. For that I am in the control flow, click on my Data Flow Task--> Properties --> Expression --> [Lookup].sqlcommand and tying my Select query.

"SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= " + (DT_STR,20,1252) @[User::LastFridayDateTime]

Which Evaluates to 

SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= 6/14/2019 5:00:00 PM

And Hence its not working due to 5:00:00 PM I guess. My question is, I want this Date to be in this format '6/14/2019 5:00:00' rather than 6/14/2019 5:00:00 PM So that the query can compile and execute.

db2 ssis lookup
2 Answers

I think you just need to wrap single quotes around the datetime value in your expression:

"SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= '" + (DT_STR,20,1252)@[User::LastFridayDateTime] + "'"

EDIT: Sure enough, you need to first cast your datetime variable to a DT_DBTIMESTAMP before you cast it again to a string. Have I mentioned how much I love the 67 different sets of SSIS datatypes?

This should work, though, as ridiculous as it seems:

"SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= '" + (DT_STR,20, 1252)(DT_DBTIMESTAMP) @[User::LastFridayDateTime] + "'"
user_57905840

Try the following command:

"SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= CONVERT(DATETIME,'" + (DT_STR,20,1252) @[User::LastFridayDateTime]  + "',102)"

You can refer to the following article to get the relevant format number if 102 didn't work:

OR

"SELECT No, Date_tran
FROM  TABLE
WHERE Date_tran  >= CAST('" + (DT_STR,20,1252) @[User::LastFridayDateTime]  + "' AS DATETIME)"
user_70312300
Related