Oracle sql error with to_char function

Question

i want to do search depend on date , when i use this statement it gives me empty result

Select * from apps.xx_fa_track where TO_CHAR(trx_date, 'mm/dd/yyyy') = '5/25/2014'

Select  to_char(sysdate, 'MM/DD/YYYY') FROM apps.xx_fa_track

Can someone please help?

I suppose mm gives you two digits of month (e.g. 05), and you compare to one-digit month (5). When in doubt, select ... from dual is your friend, it allows you to evaluate arbitrary expressions and see if they make sense. — 9000
– 9000, Commented May 25, 2014 at 9:27
yes thats work , but the problem is in my code i send date in jquery mobile formated like 5/25/2014 !! and i can't change it :( — user3673354
– user3673354, Commented May 25, 2014 at 9:34

Alex Poole · Accepted Answer · 2014-05-25 11:27:07Z

3

You can use the FM format modifier to stop the string versrion of the date having leading zeros:

where TO_CHAR(trx_date, 'FMmm/dd/yyyy') = '5/25/2014'

SQL Fiddle of the difference.

But it's generally better to convert your fixed value to the column's data type:

where trx_date = TO_DATE('5/25/2014', 'mm/dd/yyyy')

If your trx_date includes a time portion you can use a range to cover the whole day, but not sure that's needed here.

answered May 25, 2014 at 11:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

+1 for pointing out that it is better to convert the constant. However, you might need trunc(trx_date) = TO_DATE('5/25/2014', 'mm/dd/yyyy') for the same logic.

Dawid Krysiak · Accepted Answer · 2014-05-25 09:27:31Z

0

I guess you should format month in your date with two digits "05/25/2014". Like here:

Select * from apps.xx_fa_track where TO_CHAR(trx_date, 'mm/dd/yyyy') = '05/25/2014'

answered May 25, 2014 at 9:27

Dawid Krysiak

765 bronze badges

1 Comment

user3673354 Over a year ago

yes thats work , but the problem is in my code i send date in jquery mobile formated like 5/25/2014 !! and i can't change it :(

Collectives™ on Stack Overflow

Oracle sql error with to_char function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related