December 3, 2014 at 8:11 am
Hello all, I'm not really sure what level of expertise folks may have in the ODBC realm here. If there's a more appropriate category on the forum please let me know.
I'm pretty new to SSIS packages and ODBC connections. I've configured a few successfully to pull data from clients over their ODBC connections, but I'm running into a problem with one I'm trying to complete now.
The data that I'm trying to pull is stored in a view. The view grabs data from several different tables and when I try to execute my query:
SELECT Column1, Column2, Column3, Column4, STARTDATE
FROM VIEWS.DATA
WHERE (USERID BETWEEN 1 AND 1000) AND (STARTDATE >= "TO_DATE"("TO_CHAR"(CURRENT_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') - 2)
I receive the following error: "Invalid date supplied for DATA_WEST.STARTDATE_D."
This same date query has worked fine on views in the past, when the columns are named the same.
There are 5 different tables the view pulls data from. All but one of them use the column named STARTDATE, but DATA_WEST has it labeled as STARTDATE_D. There are additional columns in the View that I need, so simply pulling data from each individual table isn't an option.
Anyone have knowledge in this realm of things that can lend a hand? Let me know if there's any additional pertinent information I need to supply. Thank you for your time folks.
December 3, 2014 at 9:54 am
Herpington_McDerpington (12/3/2014)
Hello all, I'm not really sure what level of expertise folks may have in the ODBC realm here. If there's a more appropriate category on the forum please let me know.I'm pretty new to SSIS packages and ODBC connections. I've configured a few successfully to pull data from clients over their ODBC connections, but I'm running into a problem with one I'm trying to complete now.
The data that I'm trying to pull is stored in a view. The view grabs data from several different tables and when I try to execute my query:
SELECT Column1, Column2, Column3, Column4, STARTDATE
FROM VIEWS.DATA
WHERE (USERID BETWEEN 1 AND 1000) AND (STARTDATE >= "TO_DATE"("TO_CHAR"(CURRENT_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') - 2)
I receive the following error: "Invalid date supplied for DATA_WEST.STARTDATE_D."
This same date query has worked fine on views in the past, when the columns are named the same.
There are 5 different tables the view pulls data from. All but one of them use the column named STARTDATE, but DATA_WEST has it labeled as STARTDATE_D. There are additional columns in the View that I need, so simply pulling data from each individual table isn't an option.
Anyone have knowledge in this realm of things that can lend a hand? Let me know if there's any additional pertinent information I need to supply. Thank you for your time folks.
Quick questions, what is the data source (ORA Version)? Have you tried using ALTER SESSION SET NLS_DATE_FORMAT to "format" the output of CURRENT_DATE? Don't think it's an ODBC issue as such, looks more like an incompatible date format issue.
😎
December 3, 2014 at 10:06 am
Eirikur Eiriksson (12/3/2014)
Quick questions, what is the data source (ORA Version)? Have you tried using ALTER SESSION SET NLS_DATE_FORMAT to "format" the output of CURRENT_DATE? Don't think it's an ODBC issue as such, looks more like an incompatible date format issue.😎
Thanks for the response Eirikur.
I'm honestly not sure how to see the ORA Version. I don't have the ability to do a whole lot over the ODBC connection. It's a Teradata ODBC connection and the version of that is 14.10.0304, though I don't think that's what you're looking for.
As far as the ALTER SESSION question goes - No, I've not tried that as I'm not at all familiar with that.
As an aside...I can query the DATA_WEST view directly with the following where clause:
WHERE (ID BETWEEN 1 AND 1000) AND (STARTDATE >= CURRENT_DATE - 1)
But if I use that same thing to query the view that includes data from multiple views, it once again throws an error for the DATA_WEST.STARTDATE_D column having an invalid date.
December 3, 2014 at 11:39 am
Herpington_McDerpington (12/3/2014)
Eirikur Eiriksson (12/3/2014)
Quick questions, what is the data source (ORA Version)? Have you tried using ALTER SESSION SET NLS_DATE_FORMAT to "format" the output of CURRENT_DATE? Don't think it's an ODBC issue as such, looks more like an incompatible date format issue.😎
Thanks for the response Eirikur.
I'm honestly not sure how to see the ORA Version. I don't have the ability to do a whole lot over the ODBC connection. It's a Teradata ODBC connection and the version of that is 14.10.0304, though I don't think that's what you're looking for.
As far as the ALTER SESSION question goes - No, I've not tried that as I'm not at all familiar with that.
As an aside...I can query the DATA_WEST view directly with the following where clause:
WHERE (ID BETWEEN 1 AND 1000) AND (STARTDATE >= CURRENT_DATE - 1)
But if I use that same thing to query the view that includes data from multiple views, it once again throws an error for the DATA_WEST.STARTDATE_D column having an invalid date.
I suspect that I might be wrong on this, took it as Oracle:blush:(ORA), if I remember correctly there is a similar/identical syntax for date format for Teradata, been too long for me to be certain.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply