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
sql oracle
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
asked Nov 22 at 16:44
deependra679
12
add a comment |
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
sql oracle
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
asked Nov 22 at 16:44
deependra679
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 asVARCHAR2, 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_DATEandTO_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 useTO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
– Hilarion
Nov 22 at 18:16
add a comment |
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
sql oracle
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
asked Nov 22 at 16:44
deependra679
12
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
sql oracle
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
asked Nov 22 at 16:44
deependra679
12
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
asked Nov 22 at 16:44
deependra679
12
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
edited Nov 22 at 20:23
Wernfried Domscheit
23.7k42857
23.7k42857
asked Nov 22 at 16:44
deependra679
12
asked Nov 22 at 16:44
deependra679
12
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 asVARCHAR2, 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_DATEandTO_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 useTO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').
– Hilarion
Nov 22 at 18:16
add a comment |
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 asVARCHAR2, 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_DATEandTO_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 useTO_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
add a comment |
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.
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
2
When already doing substrings, then why useTO_CHARandTO_DATE? Woudn't it be easier/faster to doSUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and useDATEorTIMESTAMPcolumn type fortreatend_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 ontreatend_actalso to cover hours). Something liketreatend_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 wholeWHEREcondition (covering both the date, done previously byLIKEand time, covered by substrings).
– Hilarion
Nov 22 at 18:32
1
You can even loose theLIKE. 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
|
show 3 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',
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
Required, but never shown
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.
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
2
When already doing substrings, then why useTO_CHARandTO_DATE? Woudn't it be easier/faster to doSUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and useDATEorTIMESTAMPcolumn type fortreatend_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 ontreatend_actalso to cover hours). Something liketreatend_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 wholeWHEREcondition (covering both the date, done previously byLIKEand time, covered by substrings).
– Hilarion
Nov 22 at 18:32
1
You can even loose theLIKE. 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
|
show 3 more comments
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.
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
2
When already doing substrings, then why useTO_CHARandTO_DATE? Woudn't it be easier/faster to doSUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and useDATEorTIMESTAMPcolumn type fortreatend_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 ontreatend_actalso to cover hours). Something liketreatend_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 wholeWHEREcondition (covering both the date, done previously byLIKEand time, covered by substrings).
– Hilarion
Nov 22 at 18:32
1
You can even loose theLIKE. 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
|
show 3 more comments
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.
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
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.
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
edited Nov 22 at 18:35
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
answered Nov 22 at 17:27
Barbaros Özhan
11.7k71530
11.7k71530
2
When already doing substrings, then why useTO_CHARandTO_DATE? Woudn't it be easier/faster to doSUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and useDATEorTIMESTAMPcolumn type fortreatend_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 ontreatend_actalso to cover hours). Something liketreatend_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 wholeWHEREcondition (covering both the date, done previously byLIKEand time, covered by substrings).
– Hilarion
Nov 22 at 18:32
1
You can even loose theLIKE. 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
|
show 3 more comments
2
When already doing substrings, then why useTO_CHARandTO_DATE? Woudn't it be easier/faster to doSUBSTR(treatend_act, 12, 8) > '00:00:00'? And I fully agree, that @deependra679 should rather fix the table definition and useDATEorTIMESTAMPcolumn type fortreatend_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 ontreatend_actalso to cover hours). Something liketreatend_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 wholeWHEREcondition (covering both the date, done previously byLIKEand time, covered by substrings).
– Hilarion
Nov 22 at 18:32
1
You can even loose theLIKE. 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
|
show 3 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.
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
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%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
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
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_DATEandTO_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 useTO_TIMESTAMP(treatend_act, 'YYYY-MM-DD HH24:MI:SS,FF3').– Hilarion
Nov 22 at 18:16