I want to compare two column which has different date format
0
I want to compare two column which has different date format.
REQ_END_TIME = 03-APR-18 06.15.30.000000000 AMSENDDATE = 4/3/2018
Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY
How can I change any of it to make it similar to validate with query.
Database - Oracle
sql database oracle
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
add a comment |
0
I want to compare two column which has different date format.
REQ_END_TIME = 03-APR-18 06.15.30.000000000 AMSENDDATE = 4/3/2018
Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY
How can I change any of it to make it similar to validate with query.
Database - Oracle
sql database oracle
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
5
Convert both todate. If you are storing them with format, you are using avarchar/char, don't do that: always use the appropriate datatype.
– HoneyBadger
Nov 20 '18 at 14:28
3
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
2
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. IsSENDDATEa string, or is it actually aDATEin the database? You can find out easily with theDESCRIBEcommand -DESCRIBE TABLE_NAMEwill show the names and the data types of each column in a table calledTABLE_NAME. Please check the data type of the two columns and add your findings to your post.
– mathguy
Nov 20 '18 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 '18 at 14:55
add a comment |
0
0
0
0
I want to compare two column which has different date format.
REQ_END_TIME = 03-APR-18 06.15.30.000000000 AMSENDDATE = 4/3/2018
Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY
How can I change any of it to make it similar to validate with query.
Database - Oracle
sql database oracle
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
I want to compare two column which has different date format.
REQ_END_TIME = 03-APR-18 06.15.30.000000000 AMSENDDATE = 4/3/2018
Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY
How can I change any of it to make it similar to validate with query.
Database - Oracle
sql database oracle
sql database oracle
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
edited Nov 20 '18 at 14:30
JNevill
31.4k31544
31.4k31544
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
asked Nov 20 '18 at 14:27
Avinash SinghAvinash Singh
45
45
5
Convert both todate. If you are storing them with format, you are using avarchar/char, don't do that: always use the appropriate datatype.
– HoneyBadger
Nov 20 '18 at 14:28
3
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
2
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. IsSENDDATEa string, or is it actually aDATEin the database? You can find out easily with theDESCRIBEcommand -DESCRIBE TABLE_NAMEwill show the names and the data types of each column in a table calledTABLE_NAME. Please check the data type of the two columns and add your findings to your post.
– mathguy
Nov 20 '18 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 '18 at 14:55
add a comment |
5
Convert both todate. If you are storing them with format, you are using avarchar/char, don't do that: always use the appropriate datatype.
– HoneyBadger
Nov 20 '18 at 14:28
3
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
2
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. IsSENDDATEa string, or is it actually aDATEin the database? You can find out easily with theDESCRIBEcommand -DESCRIBE TABLE_NAMEwill show the names and the data types of each column in a table calledTABLE_NAME. Please check the data type of the two columns and add your findings to your post.
– mathguy
Nov 20 '18 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 '18 at 14:55
5
5
Convert both to
date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.– HoneyBadger
Nov 20 '18 at 14:28
Convert both to
date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.– HoneyBadger
Nov 20 '18 at 14:28
3
3
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
2
2
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is
SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.– mathguy
Nov 20 '18 at 14:51
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is
SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.– mathguy
Nov 20 '18 at 14:51
1
1
@mathguy, indeed perhaps
REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.– Wernfried Domscheit
Nov 20 '18 at 14:55
@mathguy, indeed perhaps
REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.– Wernfried Domscheit
Nov 20 '18 at 14:55
add a comment |
3 Answers
3
active
oldest
votes
0
You would convert both to dates or to strings:
where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY')will not work. Better useTO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am')provided they are varchar2
– Wernfried Domscheit
Nov 20 '18 at 15:05
add a comment |
0
You can use CAST and to_date (with the format mask)
select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')
It'll return:
03.04.18 = 03.04.18
Compiled query
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
add a comment |
0
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 '18 at 2:59
Pang
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
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%2f53395172%2fi-want-to-compare-two-column-which-has-different-date-format%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You would convert both to dates or to strings:
where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY')will not work. Better useTO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am')provided they are varchar2
– Wernfried Domscheit
Nov 20 '18 at 15:05
add a comment |
0
You would convert both to dates or to strings:
where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY')will not work. Better useTO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am')provided they are varchar2
– Wernfried Domscheit
Nov 20 '18 at 15:05
add a comment |
0
0
0
You would convert both to dates or to strings:
where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
You would convert both to dates or to strings:
where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
edited Nov 20 '18 at 18:41
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
answered Nov 20 '18 at 14:56
Gordon LinoffGordon Linoff
761k35294399
761k35294399
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY')will not work. Better useTO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am')provided they are varchar2
– Wernfried Domscheit
Nov 20 '18 at 15:05
add a comment |
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY')will not work. Better useTO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am')provided they are varchar2
– Wernfried Domscheit
Nov 20 '18 at 15:05
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2– Wernfried Domscheit
Nov 20 '18 at 15:05
to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2– Wernfried Domscheit
Nov 20 '18 at 15:05
add a comment |
0
You can use CAST and to_date (with the format mask)
select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')
It'll return:
03.04.18 = 03.04.18
Compiled query
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
add a comment |
0
You can use CAST and to_date (with the format mask)
select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')
It'll return:
03.04.18 = 03.04.18
Compiled query
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
add a comment |
0
0
0
You can use CAST and to_date (with the format mask)
select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')
It'll return:
03.04.18 = 03.04.18
Compiled query
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
You can use CAST and to_date (with the format mask)
select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')
It'll return:
03.04.18 = 03.04.18
Compiled query
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
answered Nov 20 '18 at 20:54
GeorgyGeorgy
788
788
add a comment |
add a comment |
0
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 '18 at 2:59
Pang
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
add a comment |
0
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 '18 at 2:59
Pang
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
add a comment |
0
0
0
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 '18 at 2:59
Pang
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 '18 at 2:59
Pang
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
edited Nov 21 '18 at 2:59
Pang
6,8711563101
edited Nov 21 '18 at 2:59
Pang
6,8711563101
edited Nov 21 '18 at 2:59
Pang
6,8711563101
6,8711563101
answered Nov 21 '18 at 2:51
user3439907user3439907
112
answered Nov 21 '18 at 2:51
user3439907user3439907
112
answered Nov 21 '18 at 2:51
user3439907user3439907
112
112
add a comment |
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.
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%2f53395172%2fi-want-to-compare-two-column-which-has-different-date-format%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
5
Convert both to
date. If you are storing them with format, you are using avarchar/char, don't do that: always use the appropriate datatype.– HoneyBadger
Nov 20 '18 at 14:28
3
Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 '18 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 '18 at 14:39
2
I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is
SENDDATEa string, or is it actually aDATEin the database? You can find out easily with theDESCRIBEcommand -DESCRIBE TABLE_NAMEwill show the names and the data types of each column in a table calledTABLE_NAME. Please check the data type of the two columns and add your findings to your post.– mathguy
Nov 20 '18 at 14:51
1
@mathguy, indeed perhaps
REQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.– Wernfried Domscheit
Nov 20 '18 at 14:55