ORA-01830: date format picture ends before converting entire input string 01830











up vote
0
down vote

favorite












My query:



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


Table structure:



HeatID         TREATEND_ACT(varchar2)
0001027184 2018-11-23 02:20:25,906
0001027399 2018-11-23 04:45:02,571


System Date and Time settings:



Short Date: yyyy-MM-dd
Long Date: dd MMMMM,yyyy
Short Time: hh:mm tt
Long Time: hh:mm:ss tt
No AM andPM









share|improve this question




















  • 1




    While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
    – Kaushik Nayak
    Nov 22 at 17:07












  • @deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
    – Hilarion
    Nov 22 at 18:16















up vote
0
down vote

favorite












My query:



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


Table structure:



HeatID         TREATEND_ACT(varchar2)
0001027184 2018-11-23 02:20:25,906
0001027399 2018-11-23 04:45:02,571


System Date and Time settings:



Short Date: yyyy-MM-dd
Long Date: dd MMMMM,yyyy
Short Time: hh:mm tt
Long Time: hh:mm:ss tt
No AM andPM









share|improve this question




















  • 1




    While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
    – Kaushik Nayak
    Nov 22 at 17:07












  • @deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
    – Hilarion
    Nov 22 at 18:16













up vote
0
down vote

favorite









up vote
0
down vote

favorite











My query:



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


Table structure:



HeatID         TREATEND_ACT(varchar2)
0001027184 2018-11-23 02:20:25,906
0001027399 2018-11-23 04:45:02,571


System Date and Time settings:



Short Date: yyyy-MM-dd
Long Date: dd MMMMM,yyyy
Short Time: hh:mm tt
Long Time: hh:mm:ss tt
No AM andPM









share|improve this question















My query:



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(treatend_act,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


Table structure:



HeatID         TREATEND_ACT(varchar2)
0001027184 2018-11-23 02:20:25,906
0001027399 2018-11-23 04:45:02,571


System Date and Time settings:



Short Date: yyyy-MM-dd
Long Date: dd MMMMM,yyyy
Short Time: hh:mm tt
Long Time: hh:mm:ss tt
No AM andPM






sql oracle






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 20:23









Wernfried Domscheit

23.7k42857




23.7k42857










asked Nov 22 at 16:44









deependra679

12




12








  • 1




    While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
    – Kaushik Nayak
    Nov 22 at 17:07












  • @deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
    – Hilarion
    Nov 22 at 18:16














  • 1




    While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
    – Kaushik Nayak
    Nov 22 at 17:07












  • @deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
    – Hilarion
    Nov 22 at 18:16








1




1




While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
– Kaushik Nayak
Nov 22 at 17:07






While it may be apparent from your query for some what you are trying to do, it is helpful to everybody reading your question if you explain a bit about what you were actually trying to achieve. Also, please don't store date/timestamp as VARCHAR2, it's not a good design practice.
– Kaushik Nayak
Nov 22 at 17:07














@deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
– Hilarion
Nov 22 at 18:16




@deependra679: As you are using strings (varchar2) and your conversions (TO_DATE and TO_CHAR) specify formats, your date/time settings do not play a big role. (Otherwise, remember, that it's more about your session settings, not your server settings.) If you intend to sticking to date/time as text and double conversion, you can try to use TO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
– Hilarion
Nov 22 at 18:16












1 Answer
1






active

oldest

votes

















up vote
1
down vote













Because your query is trying to convert whole string to date format of which cannot be recognized by to_date function as a whole, you may try to apply substr first as :



with pd_heat_data( treatend_act ) as
( select '2018-11-23 02:20:25,906' from dual )
select TO_CHAR(
TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS')
,'HH24:MI:SS') as "Derived Time"
from pd_heat_data;

Derived Time
------------
02:20:25


and for the exact query :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


or there's no need for extra conversion, substr might be applied directly as @Hilarion(thanks to him) pointed out :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%')
AND substr(treatend_act,1,19) > '2018-11-23 00:00:00'
AND substr(treatend_act,1,19) < '2018-11-23 06:00:00';


and as much as possible try to move your data at treatend_act to a column of type date.






share|improve this answer



















  • 2




    When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
    – Hilarion
    Nov 22 at 18:00












  • @Hilarion yeah, exactly you're right.
    – Barbaros Özhan
    Nov 22 at 18:03










  • When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
    – Hilarion
    Nov 22 at 18:26










  • The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
    – Hilarion
    Nov 22 at 18:32








  • 1




    You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
    – Hilarion
    Nov 22 at 18:50











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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435273%2fora-01830-date-format-picture-ends-before-converting-entire-input-string-01830%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








up vote
1
down vote













Because your query is trying to convert whole string to date format of which cannot be recognized by to_date function as a whole, you may try to apply substr first as :



with pd_heat_data( treatend_act ) as
( select '2018-11-23 02:20:25,906' from dual )
select TO_CHAR(
TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS')
,'HH24:MI:SS') as "Derived Time"
from pd_heat_data;

Derived Time
------------
02:20:25


and for the exact query :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


or there's no need for extra conversion, substr might be applied directly as @Hilarion(thanks to him) pointed out :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%')
AND substr(treatend_act,1,19) > '2018-11-23 00:00:00'
AND substr(treatend_act,1,19) < '2018-11-23 06:00:00';


and as much as possible try to move your data at treatend_act to a column of type date.






share|improve this answer



















  • 2




    When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
    – Hilarion
    Nov 22 at 18:00












  • @Hilarion yeah, exactly you're right.
    – Barbaros Özhan
    Nov 22 at 18:03










  • When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
    – Hilarion
    Nov 22 at 18:26










  • The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
    – Hilarion
    Nov 22 at 18:32








  • 1




    You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
    – Hilarion
    Nov 22 at 18:50















up vote
1
down vote













Because your query is trying to convert whole string to date format of which cannot be recognized by to_date function as a whole, you may try to apply substr first as :



with pd_heat_data( treatend_act ) as
( select '2018-11-23 02:20:25,906' from dual )
select TO_CHAR(
TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS')
,'HH24:MI:SS') as "Derived Time"
from pd_heat_data;

Derived Time
------------
02:20:25


and for the exact query :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


or there's no need for extra conversion, substr might be applied directly as @Hilarion(thanks to him) pointed out :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%')
AND substr(treatend_act,1,19) > '2018-11-23 00:00:00'
AND substr(treatend_act,1,19) < '2018-11-23 06:00:00';


and as much as possible try to move your data at treatend_act to a column of type date.






share|improve this answer



















  • 2




    When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
    – Hilarion
    Nov 22 at 18:00












  • @Hilarion yeah, exactly you're right.
    – Barbaros Özhan
    Nov 22 at 18:03










  • When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
    – Hilarion
    Nov 22 at 18:26










  • The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
    – Hilarion
    Nov 22 at 18:32








  • 1




    You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
    – Hilarion
    Nov 22 at 18:50













up vote
1
down vote










up vote
1
down vote









Because your query is trying to convert whole string to date format of which cannot be recognized by to_date function as a whole, you may try to apply substr first as :



with pd_heat_data( treatend_act ) as
( select '2018-11-23 02:20:25,906' from dual )
select TO_CHAR(
TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS')
,'HH24:MI:SS') as "Derived Time"
from pd_heat_data;

Derived Time
------------
02:20:25


and for the exact query :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


or there's no need for extra conversion, substr might be applied directly as @Hilarion(thanks to him) pointed out :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%')
AND substr(treatend_act,1,19) > '2018-11-23 00:00:00'
AND substr(treatend_act,1,19) < '2018-11-23 06:00:00';


and as much as possible try to move your data at treatend_act to a column of type date.






share|improve this answer














Because your query is trying to convert whole string to date format of which cannot be recognized by to_date function as a whole, you may try to apply substr first as :



with pd_heat_data( treatend_act ) as
( select '2018-11-23 02:20:25,906' from dual )
select TO_CHAR(
TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS')
,'HH24:MI:SS') as "Derived Time"
from pd_heat_data;

Derived Time
------------
02:20:25


and for the exact query :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%') AND (
( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
> '00:00:00' )
AND ( TO_CHAR(TO_DATE(substr(treatend_act,1,19),'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS')
< '06:00:00' )
);


or there's no need for extra conversion, substr might be applied directly as @Hilarion(thanks to him) pointed out :



SELECT COUNT(*)
FROM pd_heat_data
WHERE treatend_act LIKE TO_CHAR(current_date + 1,'YYYY-MM-DD%')
AND substr(treatend_act,1,19) > '2018-11-23 00:00:00'
AND substr(treatend_act,1,19) < '2018-11-23 06:00:00';


and as much as possible try to move your data at treatend_act to a column of type date.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 at 18:35

























answered Nov 22 at 17:27









Barbaros Özhan

11.7k71530




11.7k71530








  • 2




    When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
    – Hilarion
    Nov 22 at 18:00












  • @Hilarion yeah, exactly you're right.
    – Barbaros Özhan
    Nov 22 at 18:03










  • When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
    – Hilarion
    Nov 22 at 18:26










  • The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
    – Hilarion
    Nov 22 at 18:32








  • 1




    You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
    – Hilarion
    Nov 22 at 18:50














  • 2




    When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
    – Hilarion
    Nov 22 at 18:00












  • @Hilarion yeah, exactly you're right.
    – Barbaros Özhan
    Nov 22 at 18:03










  • When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
    – Hilarion
    Nov 22 at 18:26










  • The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
    – Hilarion
    Nov 22 at 18:32








  • 1




    You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
    – Hilarion
    Nov 22 at 18:50








2




2




When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
– Hilarion
Nov 22 at 18:00






When already doing substrings, then why use TO_CHAR and TO_DATE? Woudn't it be easier/faster to do SUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and use DATE or TIMESTAMP column type for treatend_act.
– Hilarion
Nov 22 at 18:00














@Hilarion yeah, exactly you're right.
– Barbaros Özhan
Nov 22 at 18:03




@Hilarion yeah, exactly you're right.
– Barbaros Özhan
Nov 22 at 18:03












When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
– Hilarion
Nov 22 at 18:26




When I saw your simplified query, I actually think there can be something more efficient (i.e. be able to use an index range scan on treatend_act also to cover hours). Something like treatend_act > TO_CHAR(current_date + 1, 'YYYY-MM-DD "00:00:00,000"') AND treatend_act < TO_CHAR(current_date + 1, 'YYYY-MM-DD "06:00:00,000"'). What do you think?
– Hilarion
Nov 22 at 18:26












The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
– Hilarion
Nov 22 at 18:32






The above would replace the whole WHERE condition (covering both the date, done previously by LIKE and time, covered by substrings).
– Hilarion
Nov 22 at 18:32






1




1




You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
– Hilarion
Nov 22 at 18:50




You can even loose the LIKE. Something like this: sqlfiddle.com/#!4/e63b02/9/0 (and the execution plan shows, that a proper range scan does work).
– Hilarion
Nov 22 at 18:50


















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.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • 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














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435273%2fora-01830-date-format-picture-ends-before-converting-entire-input-string-01830%23new-answer', 'question_page');
}
);

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







Popular posts from this blog

What visual should I use to simply compare current year value vs last year in Power BI desktop

How to ignore python UserWarning in pytest?

Alexandru Averescu