Change data type of variable in Execute SQL Task
-
I faced problem of converting data types of variables. I have two variables in the Package: Extract_Start_Date and Extract_End_Date. The data type of them - String. I need to delete data in the source table based on them. But in source table I have column date_cr with data type int. I added Execute SQL Script Task component and wrote the query: delete from T1 where date_cr between ? and ?.
Could you please help me with parameters mapping? How can I correctly convert these variables?
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Are they both in YYYYMMDD format?
There is no such thing as an 'Execute SQL Script Task component'. I think you mean an ExecuteSQL task, is that correct?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi, Phil
Yes, it's correct!
-
I imagine you have a reason for setting the variables as string in the package, but if not, why not just make them INTs?
Alternatively, create two new INT variables and use an Expression to convert the string variables to INTs, like this:
Once you have the two INTs created, just adjust your ExecSQL task to use them rather than the string variables.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
no need for further variables.
just do the conversion in sql
delete from T1 where date_cr between convert(int, ?) and convert(int, ?)
- frederico_fonseca wrote:
no need for further variables.
just do the conversion in sql
delete from T1 where date_cr between convert(int, ?) and convert(int, ?)
Or - just let SQL implicitly convert them to integer. But - I also wonder why they are set to string in the first place.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey,
I used string because there is some C# code and manipulation with source data. That's why I decided to left this format.
The main problem that I have not 20210707 string format, but such kind of string format - 2021-07-07...
-
If the string date is in the format YYYY-MM-DD then in an expression just replace the '-' with '' and convert to integer. Or - as Phil stated, create a new variable that is an integer and use an expression to convert the dashes to space.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply