Why am I seeing values of '2432-82-75 50:08:01' in Oracle DATE column?
As part of my job duties, I'm responsible for extracting data from our vendor's Oracle 11g database, and loading it into our SQL Server 2016 database. I've been doing this successfully with SSIS and the Attunity Oracle connectors.
Today I was informed that there was a new column added to the existing Invoices table on the Oracle side. There was already a DATE column called Order Date, which contains valid date values with zero'd times, like 2017-12-25 00:00:00.
The new column is called Order Date Time and is also a DATE column. When I opened up the SSIS package and pulled up the Oracle source in my DFT, I previewed the data and found the values in Order Date Time to be 2432-82-75 50:08:01. I tried converting the column with CAST and all the TO_* functions, but the conversions either failed outright, or returned a string of zeros.
TO_CHAR("Order Date Time", 'YYYYMMDDHH24MISS')
yields 00000000000000
After a bit of Googling for "Oracle date value invalid", I'm now thinking that these DATE values are actually corrupted. Am I missing anything here? Is there some sort of special Oracle-specific technique for storing time values in a DATE column that I may not be aware of?
(And yes, it does bother me quite a bit that our vendor added another DATE column instead of just using the time portion of the existing Order Date column.)
sql-server oracle date ssis etl edited Jan 20 at 21:42
Hadi
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
add a comment |
As part of my job duties, I'm responsible for extracting data from our vendor's Oracle 11g database, and loading it into our SQL Server 2016 database. I've been doing this successfully with SSIS and the Attunity Oracle connectors.
Today I was informed that there was a new column added to the existing Invoices table on the Oracle side. There was already a DATE column called Order Date, which contains valid date values with zero'd times, like 2017-12-25 00:00:00.
The new column is called Order Date Time and is also a DATE column. When I opened up the SSIS package and pulled up the Oracle source in my DFT, I previewed the data and found the values in Order Date Time to be 2432-82-75 50:08:01. I tried converting the column with CAST and all the TO_* functions, but the conversions either failed outright, or returned a string of zeros.
TO_CHAR("Order Date Time", 'YYYYMMDDHH24MISS')
yields 00000000000000
After a bit of Googling for "Oracle date value invalid", I'm now thinking that these DATE values are actually corrupted. Am I missing anything here? Is there some sort of special Oracle-specific technique for storing time values in a DATE column that I may not be aware of?
(And yes, it does bother me quite a bit that our vendor added another DATE column instead of just using the time portion of the existing Order Date column.)
sql-server oracle date ssis etl edited Jan 20 at 21:42
Hadi
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
3
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42
add a comment |
As part of my job duties, I'm responsible for extracting data from our vendor's Oracle 11g database, and loading it into our SQL Server 2016 database. I've been doing this successfully with SSIS and the Attunity Oracle connectors.
Today I was informed that there was a new column added to the existing Invoices table on the Oracle side. There was already a DATE column called Order Date, which contains valid date values with zero'd times, like 2017-12-25 00:00:00.
The new column is called Order Date Time and is also a DATE column. When I opened up the SSIS package and pulled up the Oracle source in my DFT, I previewed the data and found the values in Order Date Time to be 2432-82-75 50:08:01. I tried converting the column with CAST and all the TO_* functions, but the conversions either failed outright, or returned a string of zeros.
TO_CHAR("Order Date Time", 'YYYYMMDDHH24MISS')
yields 00000000000000
After a bit of Googling for "Oracle date value invalid", I'm now thinking that these DATE values are actually corrupted. Am I missing anything here? Is there some sort of special Oracle-specific technique for storing time values in a DATE column that I may not be aware of?
(And yes, it does bother me quite a bit that our vendor added another DATE column instead of just using the time portion of the existing Order Date column.)
sql-server oracle date ssis etl edited Jan 20 at 21:42
Hadi
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
As part of my job duties, I'm responsible for extracting data from our vendor's Oracle 11g database, and loading it into our SQL Server 2016 database. I've been doing this successfully with SSIS and the Attunity Oracle connectors.
Today I was informed that there was a new column added to the existing Invoices table on the Oracle side. There was already a DATE column called Order Date, which contains valid date values with zero'd times, like 2017-12-25 00:00:00.
The new column is called Order Date Time and is also a DATE column. When I opened up the SSIS package and pulled up the Oracle source in my DFT, I previewed the data and found the values in Order Date Time to be 2432-82-75 50:08:01. I tried converting the column with CAST and all the TO_* functions, but the conversions either failed outright, or returned a string of zeros.
TO_CHAR("Order Date Time", 'YYYYMMDDHH24MISS')
yields 00000000000000
After a bit of Googling for "Oracle date value invalid", I'm now thinking that these DATE values are actually corrupted. Am I missing anything here? Is there some sort of special Oracle-specific technique for storing time values in a DATE column that I may not be aware of?
(And yes, it does bother me quite a bit that our vendor added another DATE column instead of just using the time portion of the existing Order Date column.)
sql-server oracle date ssis etl sql-server oracle date ssis etl edited Jan 20 at 21:42
Hadi
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
edited Jan 20 at 21:42
Hadi
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
edited Jan 20 at 21:42
Hadi
17.5k62672
edited Jan 20 at 21:42
Hadi
17.5k62672
edited Jan 20 at 21:42
Hadi
17.5k62672
17.5k62672
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
asked Jan 18 at 23:24
digital.aarondigital.aaron
3,1901330
3,1901330
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
3
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42
add a comment |
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
3
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
3
3
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42
add a comment |
1 Answer
1
active
oldest
votes
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL
- You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
answered Jan 20 at 21:41
HadiHadi
17.5k62672
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54262676%2fwhy-am-i-seeing-values-of-2432-82-75-500801-in-oracle-date-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL
- You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
answered Jan 20 at 21:41
HadiHadi
17.5k62672
add a comment |
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL
- You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
answered Jan 20 at 21:41
HadiHadi
17.5k62672
add a comment |
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL
- You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
answered Jan 20 at 21:41
HadiHadi
17.5k62672
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL
- You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
answered Jan 20 at 21:41
HadiHadi
17.5k62672
answered Jan 20 at 21:41
HadiHadi
17.5k62672
answered Jan 20 at 21:41
HadiHadi
17.5k62672
answered Jan 20 at 21:41
HadiHadi
17.5k62672
17.5k62672
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54262676%2fwhy-am-i-seeing-values-of-2432-82-75-500801-in-oracle-date-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Something doesn't match here. You said a date column was added but then wonder why vendor did not use the time portion of existing column. That is inconsistent. But rather than wonder, did you ask someone who has actual access to the database to use the Oracle tools and verify some row samples? And perhaps verify the actual schema is what you think it is?
– SMor
Jan 18 at 23:28
There was already an "Order Date" column of type DATE, where all the values have "00:00:00" as the time component. A new column, "Order Date Time", also of type DATE was then added to the table today. I don't understand why they added a new DATE column, when the existing DATE column could have been used to store the order date with time value.
– digital.aaron
Jan 18 at 23:32
I have put in a question to the Oracle dba at our vendor, but speedy responses to database issues are not their strong point. I also question the competence of the Oracle dba, for this and other facepalm-y reasons.
– digital.aaron
Jan 18 at 23:34
Oracle DATE fields contain both date and time components. There's nothing special about it - they are really timestamp fields, accurate down to an interval of 1 second. I suggest logging in to the Oracle database directly to check that the date fields contain valid values. BTW - what's the data type of the SQL Server-side field?
– Bob Jarvis
Jan 18 at 23:49
3
That does indeed sound like corrupted values. It is, indeed, possible to load such values into an Oracle database (as sad as that is). See for example community.oracle.com/thread/3903746?start=0&tstart=0
– mathguy
Jan 19 at 2:42