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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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:55 am
If your input data is in a string, you cannot use date functions on it.
Use substring to extract the parts of the string you are interested in and do not use YEAR, MONTH etc at all.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 😀
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply