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 at 14:30
JNevill
31.3k31544
asked Nov 20 at 14:27
Avinash 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 at 14:30
JNevill
31.3k31544
asked Nov 20 at 14:27
Avinash 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 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 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 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 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 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 at 14:30
JNevill
31.3k31544
asked Nov 20 at 14:27
Avinash 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 at 14:30
JNevill
31.3k31544
asked Nov 20 at 14:27
Avinash Singh
45
edited Nov 20 at 14:30
JNevill
31.3k31544
asked Nov 20 at 14:27
Avinash Singh
45
edited Nov 20 at 14:30
JNevill
31.3k31544
edited Nov 20 at 14:30
JNevill
31.3k31544
edited Nov 20 at 14:30
JNevill
31.3k31544
31.3k31544
asked Nov 20 at 14:27
Avinash Singh
45
asked Nov 20 at 14:27
Avinash Singh
45
asked Nov 20 at 14:27
Avinash 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 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 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 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 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 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 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 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 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 at 14:51
1
@mathguy, indeed perhapsREQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.
– Wernfried Domscheit
Nov 20 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 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 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 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 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 at 14:39
How to convert a column to date format?
– Avinash Singh
Nov 20 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 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 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 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 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 at 14:56
Gordon Linoff
756k35291399
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 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 at 20:54
Georgy
667
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 at 2:59
Pang
6,8601563101
answered Nov 21 at 2:51
user3439907
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 at 14:56
Gordon Linoff
756k35291399
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 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 at 14:56
Gordon Linoff
756k35291399
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 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 at 14:56
Gordon Linoff
756k35291399
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 at 14:56
Gordon Linoff
756k35291399
edited Nov 20 at 18:41
answered Nov 20 at 14:56
Gordon Linoff
756k35291399
answered Nov 20 at 14:56
Gordon Linoff
756k35291399
answered Nov 20 at 14:56
Gordon Linoff
756k35291399
756k35291399
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 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 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 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 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 at 20:54
Georgy
667
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 at 20:54
Georgy
667
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 at 20:54
Georgy
667
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 at 20:54
Georgy
667
answered Nov 20 at 20:54
Georgy
667
answered Nov 20 at 20:54
Georgy
667
answered Nov 20 at 20:54
Georgy
667
667
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 at 2:59
Pang
6,8601563101
answered Nov 21 at 2:51
user3439907
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 at 2:59
Pang
6,8601563101
answered Nov 21 at 2:51
user3439907
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 at 2:59
Pang
6,8601563101
answered Nov 21 at 2:51
user3439907
112
Assuming SENDDATE is already in date format
select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
from mytable
edited Nov 21 at 2:59
Pang
6,8601563101
answered Nov 21 at 2:51
user3439907
112
edited Nov 21 at 2:59
Pang
6,8601563101
edited Nov 21 at 2:59
Pang
6,8601563101
edited Nov 21 at 2:59
Pang
6,8601563101
6,8601563101
answered Nov 21 at 2:51
user3439907
112
answered Nov 21 at 2:51
user3439907
112
answered Nov 21 at 2:51
user3439907
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 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 at 14:30
How to convert a column to date format?
– Avinash Singh
Nov 20 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 at 14:51
1
@mathguy, indeed perhaps
REQ_END_TIMEis aTIMESTAMPandSENDDATEis aDATEvalue. DefaultNLS_DATE_FORMATandNLS_TIMESTAMP_FORMATcan be completely different.– Wernfried Domscheit
Nov 20 at 14:55