Oracle - Date comparison with SYSDATE

I need to compare date with date of current day, using SYSDATE, something like this:

SELECT * FROM my_table

WHERE date_column BETWEEN TO_DATE(SYSDATE -3,'dd.mm.yyyy') AND TO_DATE(SYSDATE,'dd.mm.yyyy');

However, this produces no result....My question :

Based on accepted answer here we should NEVER EVER compare strings with date. But in other side, a SYSDATE is allready a Date data type, and we should not compare It to a date - see here.

If I replace TO_DATE with TO_CHAR in upper SQL things go working again. But TO_CHAR function converts into String, so Oracle (I pressume) needs to convert this string again to date so you force Oracle to do an implicit data type conversion.

So, what should be a correct comparison with date and SYSDATE, in order to avoid Oracle working a bit slowly ?

asked Nov 22 '18 at 7:00

Lucy82

538

4

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

– a_horse_with_no_name
Nov 22 '18 at 7:06

You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column?

– Hilarion
Nov 22 '18 at 7:10

add a comment |

I need to compare date with date of current day, using SYSDATE, something like this:

SELECT * FROM my_table

WHERE date_column BETWEEN TO_DATE(SYSDATE -3,'dd.mm.yyyy') AND TO_DATE(SYSDATE,'dd.mm.yyyy');

However, this produces no result....My question :

Based on accepted answer here we should NEVER EVER compare strings with date. But in other side, a SYSDATE is allready a Date data type, and we should not compare It to a date - see here.

So, what should be a correct comparison with date and SYSDATE, in order to avoid Oracle working a bit slowly ?

asked Nov 22 '18 at 7:00

Lucy82

538

4

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

– a_horse_with_no_name
Nov 22 '18 at 7:06

You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column?

– Hilarion
Nov 22 '18 at 7:10

add a comment |

I need to compare date with date of current day, using SYSDATE, something like this:

SELECT * FROM my_table

WHERE date_column BETWEEN TO_DATE(SYSDATE -3,'dd.mm.yyyy') AND TO_DATE(SYSDATE,'dd.mm.yyyy');

However, this produces no result....My question :

Based on accepted answer here we should NEVER EVER compare strings with date. But in other side, a SYSDATE is allready a Date data type, and we should not compare It to a date - see here.

So, what should be a correct comparison with date and SYSDATE, in order to avoid Oracle working a bit slowly ?

asked Nov 22 '18 at 7:00

Lucy82

538

I need to compare date with date of current day, using SYSDATE, something like this:

SELECT * FROM my_table

WHERE date_column BETWEEN TO_DATE(SYSDATE -3,'dd.mm.yyyy') AND TO_DATE(SYSDATE,'dd.mm.yyyy');

However, this produces no result....My question :

Based on accepted answer here we should NEVER EVER compare strings with date. But in other side, a SYSDATE is allready a Date data type, and we should not compare It to a date - see here.

So, what should be a correct comparison with date and SYSDATE, in order to avoid Oracle working a bit slowly ?

oracle date

asked Nov 22 '18 at 7:00

Lucy82

538

asked Nov 22 '18 at 7:00

Lucy82

538

asked Nov 22 '18 at 7:00

Lucy82

538

asked Nov 22 '18 at 7:00

Lucy82

538

asked Nov 22 '18 at 7:00

Lucy82

538

4

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

– a_horse_with_no_name
Nov 22 '18 at 7:06

You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column?

– Hilarion
Nov 22 '18 at 7:10

add a comment |

4

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

– a_horse_with_no_name
Nov 22 '18 at 7:06

You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column?

– Hilarion
Nov 22 '18 at 7:10

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

– a_horse_with_no_name
Nov 22 '18 at 7:06

You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column?

– Hilarion
Nov 22 '18 at 7:10

add a comment |

1 Answer
1

active

oldest

votes

You should not need to call either TO_DATE or TO_CHAR:

SELECT *

FROM my_table

WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

1

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

1

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

1

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

1

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

|
show 7 more comments

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425441%2foracle-date-comparison-with-sysdate%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You should not need to call either TO_DATE or TO_CHAR:

SELECT *

FROM my_table

WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

1

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

1

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

1

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

1

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

|
show 7 more comments

You should not need to call either TO_DATE or TO_CHAR:

SELECT *

FROM my_table

WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

1

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

1

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

1

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

1

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

|
show 7 more comments

You should not need to call either TO_DATE or TO_CHAR:

SELECT *

FROM my_table

WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

You should not need to call either TO_DATE or TO_CHAR:

SELECT *

FROM my_table

WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

edited Nov 22 '18 at 7:09

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

answered Nov 22 '18 at 7:03

Tim Biegeleisen

221k1388141

1

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

1

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

1

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

1

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

|
show 7 more comments

1

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

1

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

1

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

1

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

The TO_DATE use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE or even date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)

– Hilarion
Nov 22 '18 at 7:07

@Hilarion I agree strongly with your second query.

– Tim Biegeleisen
Nov 22 '18 at 7:10

The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table.

– Tim Biegeleisen
Nov 22 '18 at 7:19

@Hilarion A simple index on date_column might work, if that index also covered the other columns included with SELECT *.

– Tim Biegeleisen
Nov 22 '18 at 7:52

" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow" - truncating sysdate won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model.

– APC
Nov 22 '18 at 8:12

|
show 7 more comments

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg