oracle how can check if 'time' not date between two date Fields with time
-1
Q1 - i have this task where i want to check if there is no overlap in "time" between two dates fields (start_date , end_date) and their time
, the time i want to check is in the same date with the start and end date
any ideas or tips
Q2 -
select
extract( hour from
to_timestamp(
to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
Returns :
ora-01722 invalid number
My NLS_TIMESTAMP_FORMAT is 'DD-MON-YYYY HH24:MI:SS'
sql oracle
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
add a comment |
-1
Q1 - i have this task where i want to check if there is no overlap in "time" between two dates fields (start_date , end_date) and their time
, the time i want to check is in the same date with the start and end date
any ideas or tips
Q2 -
select
extract( hour from
to_timestamp(
to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
Returns :
ora-01722 invalid number
My NLS_TIMESTAMP_FORMAT is 'DD-MON-YYYY HH24:MI:SS'
sql oracle
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...
– Raymond Nijland
Dec 29 '18 at 16:25
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11
add a comment |
-1
-1
-1
Q1 - i have this task where i want to check if there is no overlap in "time" between two dates fields (start_date , end_date) and their time
, the time i want to check is in the same date with the start and end date
any ideas or tips
Q2 -
select
extract( hour from
to_timestamp(
to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
Returns :
ora-01722 invalid number
My NLS_TIMESTAMP_FORMAT is 'DD-MON-YYYY HH24:MI:SS'
sql oracle
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
Q1 - i have this task where i want to check if there is no overlap in "time" between two dates fields (start_date , end_date) and their time
, the time i want to check is in the same date with the start and end date
any ideas or tips
Q2 -
select
extract( hour from
to_timestamp(
to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
Returns :
ora-01722 invalid number
My NLS_TIMESTAMP_FORMAT is 'DD-MON-YYYY HH24:MI:SS'
sql oracle
sql oracle
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
edited Dec 29 '18 at 23:36
Barbaros Özhan
12.8k71532
12.8k71532
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
asked Dec 29 '18 at 16:17
العمدة أحمدالعمدة أحمد
62
62
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...
– Raymond Nijland
Dec 29 '18 at 16:25
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11
add a comment |
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...
– Raymond Nijland
Dec 29 '18 at 16:25
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?
... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...– Raymond Nijland
Dec 29 '18 at 16:25
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?
... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...– Raymond Nijland
Dec 29 '18 at 16:25
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11
add a comment |
1 Answer
1
active
oldest
votes
0
Q1 : you can compare dates using regular comparison operators, so to check if a given date belongs to a date interval, you need :
...my_date >= start_date AND my_date <= end_date
Or :
...my_date BETWEEN start_date AND end_date
Q2 - This does not work because of the TO_CHAR call, that expects a date as first argument (not a string) and returns a string, which is no good for consumption by TO_TIMESTAMP.
You should skip TO_CHAR and start with TO_TIMESTAMP :
select
extract( hour from
to_timestamp('04/04/2018 04:05:05', 'dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
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',
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');
});
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%2f53971212%2foracle-how-can-check-if-time-not-date-between-two-date-fields-with-time%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
0
Q1 : you can compare dates using regular comparison operators, so to check if a given date belongs to a date interval, you need :
...my_date >= start_date AND my_date <= end_date
Or :
...my_date BETWEEN start_date AND end_date
Q2 - This does not work because of the TO_CHAR call, that expects a date as first argument (not a string) and returns a string, which is no good for consumption by TO_TIMESTAMP.
You should skip TO_CHAR and start with TO_TIMESTAMP :
select
extract( hour from
to_timestamp('04/04/2018 04:05:05', 'dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
add a comment |
0
Q1 : you can compare dates using regular comparison operators, so to check if a given date belongs to a date interval, you need :
...my_date >= start_date AND my_date <= end_date
Or :
...my_date BETWEEN start_date AND end_date
Q2 - This does not work because of the TO_CHAR call, that expects a date as first argument (not a string) and returns a string, which is no good for consumption by TO_TIMESTAMP.
You should skip TO_CHAR and start with TO_TIMESTAMP :
select
extract( hour from
to_timestamp('04/04/2018 04:05:05', 'dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
add a comment |
0
0
0
Q1 : you can compare dates using regular comparison operators, so to check if a given date belongs to a date interval, you need :
...my_date >= start_date AND my_date <= end_date
Or :
...my_date BETWEEN start_date AND end_date
Q2 - This does not work because of the TO_CHAR call, that expects a date as first argument (not a string) and returns a string, which is no good for consumption by TO_TIMESTAMP.
You should skip TO_CHAR and start with TO_TIMESTAMP :
select
extract( hour from
to_timestamp('04/04/2018 04:05:05', 'dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
Q1 : you can compare dates using regular comparison operators, so to check if a given date belongs to a date interval, you need :
...my_date >= start_date AND my_date <= end_date
Or :
...my_date BETWEEN start_date AND end_date
Q2 - This does not work because of the TO_CHAR call, that expects a date as first argument (not a string) and returns a string, which is no good for consumption by TO_TIMESTAMP.
You should skip TO_CHAR and start with TO_TIMESTAMP :
select
extract( hour from
to_timestamp('04/04/2018 04:05:05', 'dd/mm/yyyy hh24:mi:ss')
)
) as v_to
from dual;
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
edited Dec 29 '18 at 18:29
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
answered Dec 29 '18 at 16:58
GMBGMB
8,3962623
8,3962623
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
add a comment |
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
thank you very much , it works fine i missed that to_char takes date not string but what about my first question
– العمدة أحمد
Dec 29 '18 at 18:05
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
for my Q1 : i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:17
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
Ok - updated my answer for Q1
– GMB
Dec 29 '18 at 18:30
add a comment |
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');
});
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%2f53971212%2foracle-how-can-check-if-time-not-date-between-two-date-fields-with-time%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
"TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype." "TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:" So you need to use a CAST i geuss to convert datetime datatype into a timestamp datatype?
... to_timestamp(CAST(to_char('04/04/2018 04:05:05','dd/mm/yyyy hh24:mi:ss')) AS timestamp)...– Raymond Nijland
Dec 29 '18 at 16:25
Q1 : please define what you call « a time overlap between two dates »
– GMB
Dec 29 '18 at 17:02
i had a time with the same date as the stored start and end date coming **** i have to check if the new time dose not comes between the the stored time in the start and end date **** E.g. start = ' 04/04/2018 10:30:00 ' **** end = ' 04/04/2018 12:30:00 ' **** if the new = '04/04/2018 11:00:00 ' return error msg if not take it
– العمدة أحمد
Dec 29 '18 at 18:11