Converting date to string SQL

Question

I am building report in data warehouse which relay on comparing daily checks and payments for one of the restaurants. I need to be able to filter data on date field however it needs to be compared with string that looks like US date format but is string so

Select a.* from 
xx a, xy b
where 
a.payment_date = b.check_date

Format of a.payment_date is DD-MON-YY(date) and format of b.check_date is MM/DD/YYYY however it is a string. Any pointers to most efficient ways of solving this problem greatly appreciated.

Since you're asking about efficiency, doing JOIN with conversion operators will hurt performance in a bad way. The efficient way would be to add a real date column. Sadly, it's seldom the realistic one. — Joachim Isaksson
– Joachim Isaksson, Commented Feb 15, 2013 at 12:48
It is old legacy system that spits out strings rather than dates. — Jestem_z_Kozanowa
– Jestem_z_Kozanowa, Commented Feb 15, 2013 at 12:53
@Lomo_effect A trigger that converts the inserted string to a new date column on insert/update could be a solution then. It would speed things up when doing JOIN for sure, but the work involved may not be worth the speed gain if the query isn't time sensitive. — Joachim Isaksson
– Joachim Isaksson, Commented Feb 15, 2013 at 13:23
@JoachimIsaksson what is better : converting string into date and comparing dates or converting date into string and comparing strings ?? I cannot add columns, build triggers or anything like this as I operate on quite large WH. — Jestem_z_Kozanowa
– Jestem_z_Kozanowa, Commented Feb 15, 2013 at 13:46

João Silva · Accepted Answer · 2013-02-15 12:43:44Z

2

Convert check_date from string to date using TO_DATE():

SELECT a.*
FROM xx a, xy b
WHERE a.payment_date = TO_DATE(b.check_date, 'mm/dd/yyyy')

Or you can do the other way around, using TO_CHAR().

answered Feb 15, 2013 at 12:43

João Silva

91.8k29 gold badges156 silver badges158 bronze badges

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

2 Comments

Jestem_z_Kozanowa Over a year ago

Thx, Joao it seems quite simple but yet proved to be very effective.

Art Over a year ago

@Lomo_effect - To compare dates you should always apply TRUNC() in front of dates unless you are trying to compare date and time.

Dominik Sandjaja · Accepted Answer · 2013-02-15 12:44:10Z

1

Convert both String dates into real dates (http://www.techonthenet.com/oracle/functions/to_date.php) so you can compare them date-wise.

answered Feb 15, 2013 at 12:44

Dominik Sandjaja

6,5166 gold badges56 silver badges81 bronze badges

Comments

Sunny Tiwari · Accepted Answer · 2013-02-15 12:46:02Z

0

i think this may work fr you

Select a.* from xx a, xy b where convert(datetime,a.payment_date) = convert(datetime,b.check_date)

answered Feb 15, 2013 at 12:46

Sunny Tiwari

751 silver badge9 bronze badges

1 Comment

Dan Bracuk Over a year ago

oracle doesn't use convert. It uses to_date.

Collectives™ on Stack Overflow

Converting date to string SQL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related