September 29, 2014 at 3:29 am
i have a date coming in like this
21/03/2018
I keep getting an error. do I have to convert it I tried the following code to try and convert it but it doesn't work
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
any ideas what the best way to get the date into my table
September 29, 2014 at 3:34 am
ronan.healy (9/29/2014)
i have a date coming in like this21/03/2018
I keep getting an error. do I have to convert it I tried the following code to try and convert it but it doesn't work
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
any ideas what the best way to get the date into my table
Try putting it into YYYYMMDD format (still text, using a derived column) and inserting that.
September 29, 2014 at 3:34 am
What is the error?
To what data type are you converting?
What is the locale used in the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 29, 2014 at 3:41 am
Phil Parkin (9/29/2014)
ronan.healy (9/29/2014)
i have a date coming in like this21/03/2018
I keep getting an error. do I have to convert it I tried the following code to try and convert it but it doesn't work
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
any ideas what the best way to get the date into my table
Try putting it into YYYYMMDD format (still text, using a derived column) and inserting that.
does this code not change it to yyyymmdd
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
September 29, 2014 at 3:44 am
ronan.healy (9/29/2014)
Phil Parkin (9/29/2014)
ronan.healy (9/29/2014)
i have a date coming in like this21/03/2018
I keep getting an error. do I have to convert it I tried the following code to try and convert it but it doesn't work
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
any ideas what the best way to get the date into my table
Try putting it into YYYYMMDD format (still text, using a derived column) and inserting that.
does this code not change it to yyyymmdd
ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)DAY([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)YEAR([Column 6])
No, this is dd/mm/yyyy.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 29, 2014 at 3:49 am
when I use that code even if I switch it around I get the following error
TITLE: Microsoft Visual Studio
------------------------------
Error at PortVal InboundLoad [Derived Column 3 [1969]]: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Error at PortVal InboundLoad [Derived Column 3 [1969]]: Evaluating function "YEAR" failed with error code 0xC0047089.
Error at PortVal InboundLoad [Derived Column 3 [1969]]: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Error at PortVal InboundLoad [Derived Column 3 [1969]]: Computing the expression "ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)YEAR([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)DAY([Column 6])" failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.
Error at PortVal InboundLoad [Derived Column 3 [1969]]: The expression "ISNULL([Column 6]) || (DT_WSTR,12)YEAR([Column 6]) == "1901" ? NULL(DT_WSTR,38) : (DT_WSTR,12)YEAR([Column 6]) + "/" + (DT_WSTR,12)MONTH([Column 6]) + "/" + (DT_WSTR,12)DAY([Column 6])" on "output column "MDate" (2057)" is not valid.
Error at PortVal InboundLoad [Derived Column 3 [1969]]: Failed to set property "Expression" on "output column "MDate" (2057)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
September 29, 2014 at 3:54 am
The YEAR function (and MONTH and DAY) only work on date data types, not on strings.
You'll get these errors when your input column is a string data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 29, 2014 at 3:56 am
Koen Verbeeck (9/29/2014)
The YEAR function (and MONTH and DAY) only work on date data types, not on strings.You'll get these errors when your input column is a string data type.
They don't call you Speedy Verbeeck for nothing 😀
September 29, 2014 at 4:01 am
Phil Parkin (9/29/2014)
Koen Verbeeck (9/29/2014)
The YEAR function (and MONTH and DAY) only work on date data types, not on strings.You'll get these errors when your input column is a string data type.
They don't call you Speedy Verbeeck for nothing 😀
True true 😉 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply