CODEWITHSUNDEEP
sqloracle-database
user3673354
user3673354

Reputation: 9

Oracle sql error with to_char function

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?

Upvotes: 0

Views: 889

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 3

Dawid Krysiak
Dawid Krysiak

Reputation: 51

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'

Upvotes: 0

Related Questions