Compare Dates in different format oracle sql

Question

I have dates in different format :

Date 1 : 30-JAN-14

Date 2 : 2014-01-30 10:06:04.0

I am trying to compare the two dates using the following :

to_char(date2,'YYYY-MM-DD HH24:MM:SS') <> to_char(date1,'dd-MON-YY')

But it is giving the following error for the date1 part :

Lietral does not match format String

I tried comparing them directly

date2<>date1

I am gtting the same error again

EDIT : Date2 is varchar and Date1 is DATE

Do you have dates or strings? You talk about dates but dates do not have formats. Strings that represent dates have a format. But then it wouldn't make sense to call to_char on a character string, it would only make sense to call to_date on character strings. Also, what would equality look like to you? Do you want to ignore the time component on the second value when comparing the two? — Justin Cave
– Justin Cave, Commented Sep 11, 2014 at 2:07
Date2 is a varchar column Date 1 column is of type DATE 2) I want to ignore the time component of Date2. I just want to check if the two dates are unequal — user2133404
– user2133404, Commented Sep 11, 2014 at 2:10

sgeddes · Accepted Answer · 2014-09-11 02:21:14Z

1

Probably easiest to use substr and truncate the timestamp from the date2 column. Then you can use to_date to compare:

select *
from sometable
where date1 <> to_date(substr(date2,1,10), 'YYYY-MM-DD')

SQL Fiddle Demo

answered Sep 11, 2014 at 2:21

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2133404 Over a year ago

Thanks . I am not getting any errors. But the two dates are being treated differently. I want 30-JAN-14 and 2014-01-30 TIME as equal. Can you please help me with this

sgeddes Over a year ago

@user2133404 -- did you review the fiddle? It returns those as the same based on your sample data.

isea · Accepted Answer · 2014-09-11 02:09:26Z

0

You should be Date type conversion string is the same format for comparison

answered Sep 11, 2014 at 2:09

isea

1

Comments

devbetter · Accepted Answer · 2014-09-11 02:15:44Z

0

You can try this. to_char(date2,'YYYY-MM-DD') <> to_char(date1,'YYYY-MM-DD')

answered Sep 11, 2014 at 2:15

devbetter

212 bronze badges

2 Comments

user2133404 Over a year ago

Invalid Number - I think its in date 1

user3402040 Over a year ago

with sysdate not works this method :) he return dd/mm/yy and not dd/mm/yyyy

Collectives™ on Stack Overflow

Compare Dates in different format oracle sql

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related