DATE queries using BETWEEN on oracle view

up vote
0
down vote

favorite

i have a problem with a date parameter query using 'between' operator in oracle view, when i do this query :

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN to_date(sysdate - 1, 'DD-MON-YY') AND to_date(sysdate, 'DD-MON-YY');

it does not give me the records (actualy i have any record on that date).

I try using 'in' operator, but still not give me the records.

Please throw some information for this.

*checker_date defined as date

oracle

share|improve this question

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

• 
  
  
  

  
  

  
  
  
  
  
  

  
  What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
  – Do Nhu Vy
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  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
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  You probably need trunc(sysdate-1)
  – Marmite Bomber
  14 hours ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

favorite

i have a problem with a date parameter query using 'between' operator in oracle view, when i do this query :

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN to_date(sysdate - 1, 'DD-MON-YY') AND to_date(sysdate, 'DD-MON-YY');

it does not give me the records (actualy i have any record on that date).

I try using 'in' operator, but still not give me the records.

Please throw some information for this.

*checker_date defined as date

oracle

share|improve this question

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

• 
  
  
  

  
  

  
  
  
  
  
  

  
  What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
  – Do Nhu Vy
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  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
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  You probably need trunc(sysdate-1)
  – Marmite Bomber
  14 hours ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

favorite

up vote
0
down vote

favorite

i have a problem with a date parameter query using 'between' operator in oracle view, when i do this query :

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN to_date(sysdate - 1, 'DD-MON-YY') AND to_date(sysdate, 'DD-MON-YY');

it does not give me the records (actualy i have any record on that date).

I try using 'in' operator, but still not give me the records.

Please throw some information for this.

*checker_date defined as date

oracle

share|improve this question

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

i have a problem with a date parameter query using 'between' operator in oracle view, when i do this query :

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN to_date(sysdate - 1, 'DD-MON-YY') AND to_date(sysdate, 'DD-MON-YY');

it does not give me the records (actualy i have any record on that date).

I try using 'in' operator, but still not give me the records.

Please throw some information for this.

*checker_date defined as date

oracle

oracle

share|improve this question

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

share|improve this question

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

share|improve this question

share|improve this question

edited 14 hours ago

edited 14 hours ago

edited 14 hours ago

asked 14 hours ago

Majesty Eksa Permana

443

asked 14 hours ago

Majesty Eksa Permana

443

asked 14 hours ago

Majesty Eksa Permana

443

443

• 
  
  
  

  
  

  
  
  
  
  
  

  
  What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
  – Do Nhu Vy
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  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
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  You probably need trunc(sysdate-1)
  – Marmite Bomber
  14 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
  – Do Nhu Vy
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  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
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  You probably need trunc(sysdate-1)
  – Marmite Bomber
  14 hours ago
  

  
  
  
  

What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
– Do Nhu Vy
14 hours ago

What is your MY_VIEW and your original database tables? Everyone cannot answer if don't know about it.
– Do Nhu Vy
14 hours ago

3

3

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
14 hours ago

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
14 hours ago

2

2

You probably need trunc(sysdate-1)
– Marmite Bomber
14 hours ago

You probably need trunc(sysdate-1)
– Marmite Bomber
14 hours ago

add a comment | 

3 Answers
3

active

oldest

votes

up vote
3
down vote

Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with.

You probably want

AND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)

Most probably this will still not give you want you want as that would not include rows from "today". trunc(sysdate) means "today at midnight" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:

AND CHECKER_DATE >= trunc(sysdate) - 1 

AND CHECKER_DATE < trunc(sysdate) + 1

trunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is "today".

---

All the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

add a comment | 

up vote
0
down vote

You can try:

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);

Oracle advises against using to_date for date. Also trunc is here because Trunc removes the time component.

share|improve this answer

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Updated. Thanks
  – Gauravsa
  14 hours ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

may be records are not 'Active' or 'Active' a not-existing value in one of the reference tables if any and trunc(sysdate -1) and trunc(sysdate) would help

share|improve this answer

answered 8 hours ago

user3439907

111

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371930%2fdate-queries-using-between-on-oracle-view%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
down vote

Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with.

You probably want

AND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)

Most probably this will still not give you want you want as that would not include rows from "today". trunc(sysdate) means "today at midnight" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:

AND CHECKER_DATE >= trunc(sysdate) - 1 

AND CHECKER_DATE < trunc(sysdate) + 1

trunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is "today".

---

All the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

add a comment | 

up vote
3
down vote

Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with.

You probably want

AND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)

Most probably this will still not give you want you want as that would not include rows from "today". trunc(sysdate) means "today at midnight" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:

AND CHECKER_DATE >= trunc(sysdate) - 1 

AND CHECKER_DATE < trunc(sysdate) + 1

trunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is "today".

---

All the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

add a comment | 

up vote
3
down vote

up vote
3
down vote

Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with.

You probably want

AND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)

Most probably this will still not give you want you want as that would not include rows from "today". trunc(sysdate) means "today at midnight" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:

AND CHECKER_DATE >= trunc(sysdate) - 1 

AND CHECKER_DATE < trunc(sysdate) + 1

trunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is "today".

---

All the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with.

You probably want

AND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)

Most probably this will still not give you want you want as that would not include rows from "today". trunc(sysdate) means "today at midnight" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:

AND CHECKER_DATE >= trunc(sysdate) - 1 

AND CHECKER_DATE < trunc(sysdate) + 1

trunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is "today".

---

All the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

share|improve this answer

share|improve this answer

answered 14 hours ago

a_horse_with_no_name

286k45429526

answered 14 hours ago

a_horse_with_no_name

286k45429526

answered 14 hours ago

a_horse_with_no_name

286k45429526

286k45429526

add a comment | 

add a comment | 

up vote
0
down vote

You can try:

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);

Oracle advises against using to_date for date. Also trunc is here because Trunc removes the time component.

share|improve this answer

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Updated. Thanks
  – Gauravsa
  14 hours ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

You can try:

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);

Oracle advises against using to_date for date. Also trunc is here because Trunc removes the time component.

share|improve this answer

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Updated. Thanks
  – Gauravsa
  14 hours ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

up vote
0
down vote

You can try:

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);

Oracle advises against using to_date for date. Also trunc is here because Trunc removes the time component.

share|improve this answer

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

You can try:

SELECT * 

FROM MY_VIEW 

WHERE STATUS = 'Active' 

AND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);

Oracle advises against using to_date for date. Also trunc is here because Trunc removes the time component.

share|improve this answer

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

share|improve this answer

share|improve this answer

edited 14 hours ago

edited 14 hours ago

edited 14 hours ago

answered 14 hours ago

Gauravsa

1,4261816

answered 14 hours ago

Gauravsa

1,4261816

answered 14 hours ago

Gauravsa

1,4261816

1,4261816

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Updated. Thanks
  – Gauravsa
  14 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Updated. Thanks
  – Gauravsa
  14 hours ago
  

  
  
  
  

Updated. Thanks
– Gauravsa
14 hours ago

Updated. Thanks
– Gauravsa
14 hours ago

add a comment | 

up vote
0
down vote

may be records are not 'Active' or 'Active' a not-existing value in one of the reference tables if any and trunc(sysdate -1) and trunc(sysdate) would help

share|improve this answer

answered 8 hours ago

user3439907

111

add a comment | 

up vote
0
down vote

may be records are not 'Active' or 'Active' a not-existing value in one of the reference tables if any and trunc(sysdate -1) and trunc(sysdate) would help

share|improve this answer

answered 8 hours ago

user3439907

111

add a comment | 

up vote
0
down vote

up vote
0
down vote

may be records are not 'Active' or 'Active' a not-existing value in one of the reference tables if any and trunc(sysdate -1) and trunc(sysdate) would help

share|improve this answer

answered 8 hours ago

user3439907

111

may be records are not 'Active' or 'Active' a not-existing value in one of the reference tables if any and trunc(sysdate -1) and trunc(sysdate) would help

share|improve this answer

answered 8 hours ago

user3439907

111

share|improve this answer

share|improve this answer

answered 8 hours ago

user3439907

111

answered 8 hours ago

user3439907

111

answered 8 hours ago

user3439907

111

111

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371930%2fdate-queries-using-between-on-oracle-view%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

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

Name

Email

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

Name

Email

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

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

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