Oracle to_date return incorrect result
I have a table 4 columns - Code, Status, EffectiveDate (EFF_DT), EndDate (END_DT). All the columns are Varchar2 type. EFF_DT and END_DT has ISO format date (YYYY-MM-DD) with NULL values for few rows. Need to get the rows which has END_DT greater than today's date.
While executing the below query, it returns all the Not NULL rows for END_DT. Do not compare the END_DT at all.
select code, status, EFF_DT, END_DT
from (
select CODE, EFF_DT, Status,to_date("END_DT" ,'YYYY-MM-DD' ) as END_DT
from xxx.ZZZ
) TAB
where to_date(TAB.END_DT ,'DD-MM-YY' ) > to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')
ORDER BY 1 ASC
But the below query compares the END_DT and returns the result properly -
SELECT "TAB"."CODE" , "TAB"."STATUS" AS "STATUS" , "TAB"."EFF_DT" , "TAB"."END_DT"
FROM xxx.ZZZ "TAB"
WHERE ( (to_date("TAB"."END_DT",'YYYY-MM-DD') > to_date(CAST(CURRENT_TIMESTAMP as Date), 'YY-MM-DD')) )
ORDER BY 1 ASC
What is going wrong with the 1st query?
I see difference in return value of END_DT.
For the 1st query, the data is coming like -
while as for the 2nd query, the data is coming like
oracle11g to-date
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
add a comment |
I have a table 4 columns - Code, Status, EffectiveDate (EFF_DT), EndDate (END_DT). All the columns are Varchar2 type. EFF_DT and END_DT has ISO format date (YYYY-MM-DD) with NULL values for few rows. Need to get the rows which has END_DT greater than today's date.
While executing the below query, it returns all the Not NULL rows for END_DT. Do not compare the END_DT at all.
select code, status, EFF_DT, END_DT
from (
select CODE, EFF_DT, Status,to_date("END_DT" ,'YYYY-MM-DD' ) as END_DT
from xxx.ZZZ
) TAB
where to_date(TAB.END_DT ,'DD-MM-YY' ) > to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')
ORDER BY 1 ASC
But the below query compares the END_DT and returns the result properly -
SELECT "TAB"."CODE" , "TAB"."STATUS" AS "STATUS" , "TAB"."EFF_DT" , "TAB"."END_DT"
FROM xxx.ZZZ "TAB"
WHERE ( (to_date("TAB"."END_DT",'YYYY-MM-DD') > to_date(CAST(CURRENT_TIMESTAMP as Date), 'YY-MM-DD')) )
ORDER BY 1 ASC
What is going wrong with the 1st query?
I see difference in return value of END_DT.
For the 1st query, the data is coming like -
while as for the 2nd query, the data is coming like
oracle11g to-date
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
replaceto_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')withcurrent_date. Never callto_date()with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with
– a_horse_with_no_name
Nov 13 '18 at 17:16
1
Why on earth are you storingDATEvalues in aVARCHARcolumn? That is a horrible idea.
– a_horse_with_no_name
Nov 13 '18 at 17:17
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57
add a comment |
I have a table 4 columns - Code, Status, EffectiveDate (EFF_DT), EndDate (END_DT). All the columns are Varchar2 type. EFF_DT and END_DT has ISO format date (YYYY-MM-DD) with NULL values for few rows. Need to get the rows which has END_DT greater than today's date.
While executing the below query, it returns all the Not NULL rows for END_DT. Do not compare the END_DT at all.
select code, status, EFF_DT, END_DT
from (
select CODE, EFF_DT, Status,to_date("END_DT" ,'YYYY-MM-DD' ) as END_DT
from xxx.ZZZ
) TAB
where to_date(TAB.END_DT ,'DD-MM-YY' ) > to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')
ORDER BY 1 ASC
But the below query compares the END_DT and returns the result properly -
SELECT "TAB"."CODE" , "TAB"."STATUS" AS "STATUS" , "TAB"."EFF_DT" , "TAB"."END_DT"
FROM xxx.ZZZ "TAB"
WHERE ( (to_date("TAB"."END_DT",'YYYY-MM-DD') > to_date(CAST(CURRENT_TIMESTAMP as Date), 'YY-MM-DD')) )
ORDER BY 1 ASC
What is going wrong with the 1st query?
I see difference in return value of END_DT.
For the 1st query, the data is coming like -
while as for the 2nd query, the data is coming like
oracle11g to-date
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
I have a table 4 columns - Code, Status, EffectiveDate (EFF_DT), EndDate (END_DT). All the columns are Varchar2 type. EFF_DT and END_DT has ISO format date (YYYY-MM-DD) with NULL values for few rows. Need to get the rows which has END_DT greater than today's date.
While executing the below query, it returns all the Not NULL rows for END_DT. Do not compare the END_DT at all.
select code, status, EFF_DT, END_DT
from (
select CODE, EFF_DT, Status,to_date("END_DT" ,'YYYY-MM-DD' ) as END_DT
from xxx.ZZZ
) TAB
where to_date(TAB.END_DT ,'DD-MM-YY' ) > to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')
ORDER BY 1 ASC
But the below query compares the END_DT and returns the result properly -
SELECT "TAB"."CODE" , "TAB"."STATUS" AS "STATUS" , "TAB"."EFF_DT" , "TAB"."END_DT"
FROM xxx.ZZZ "TAB"
WHERE ( (to_date("TAB"."END_DT",'YYYY-MM-DD') > to_date(CAST(CURRENT_TIMESTAMP as Date), 'YY-MM-DD')) )
ORDER BY 1 ASC
What is going wrong with the 1st query?
I see difference in return value of END_DT.
For the 1st query, the data is coming like -
while as for the 2nd query, the data is coming like
oracle11g to-date
oracle11g to-date
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
edited Nov 13 '18 at 17:15
a_horse_with_no_name
295k46451546
295k46451546
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
asked Nov 13 '18 at 17:03
Partha DasguptaPartha Dasgupta
61
61
replaceto_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')withcurrent_date. Never callto_date()with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with
– a_horse_with_no_name
Nov 13 '18 at 17:16
1
Why on earth are you storingDATEvalues in aVARCHARcolumn? That is a horrible idea.
– a_horse_with_no_name
Nov 13 '18 at 17:17
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57
add a comment |
replaceto_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')withcurrent_date. Never callto_date()with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with
– a_horse_with_no_name
Nov 13 '18 at 17:16
1
Why on earth are you storingDATEvalues in aVARCHARcolumn? That is a horrible idea.
– a_horse_with_no_name
Nov 13 '18 at 17:17
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57
replace
to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY') with current_date. Never call to_date() with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with– a_horse_with_no_name
Nov 13 '18 at 17:16
replace
to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY') with current_date. Never call to_date() with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with– a_horse_with_no_name
Nov 13 '18 at 17:16
1
1
Why on earth are you storing
DATE values in a VARCHAR column? That is a horrible idea.– a_horse_with_no_name
Nov 13 '18 at 17:17
Why on earth are you storing
DATE values in a VARCHAR column? That is a horrible idea.– a_horse_with_no_name
Nov 13 '18 at 17:17
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57
add a comment |
0
active
oldest
votes
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%2f53286119%2foracle-to-date-return-incorrect-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53286119%2foracle-to-date-return-incorrect-result%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
replace
to_date(CAST(CURRENT_TIMESTAMP as Date), 'DD-MM-YY')withcurrent_date. Never callto_date()with a parameter that is already a date or timestamp values. That only converts the date to a varchar just to convert that back to a date which it was to begin with– a_horse_with_no_name
Nov 13 '18 at 17:16
1
Why on earth are you storing
DATEvalues in aVARCHARcolumn? That is a horrible idea.– a_horse_with_no_name
Nov 13 '18 at 17:17
Thanks, @a_horse_with_no_name ! It worked!
– Partha Dasgupta
Nov 14 '18 at 10:57