sql - Oracle - get current date formatted

Question

I've been wondering - I have a lot of use with sysdate on my system, and when comparing it to my date columns I have to use trunc(sysdate) since the format of sysdate is DD/MM/YYYY HH24:MI:SS .

I looked over the internet for other functions to return current date on format of DD/MM/YYYY but had no luck, current_date , current_timestamp and ETC also gives me the hours format..

I need this for better performance when comparing an indexed date column to the current date.

So , anybody know of a system function that returns the desired format? or a way to bypass it?

Answer 1

If your "date" column is of type DATE, then you do not need to be concerned with "format". You need to compare trunc(column_name)to trunc(sysdate). And if that is causing serious performance issues because the use of a function eliminates the use of the index, then create a function-based index. A less elegant solution would be

WHERE mydatecol > trunc(sysdate) AND mydatecol < trunc(sysdate +1)

What you do NOT want to do is confuse the binary concept of DATE with the character string representation of a date. A character string is just a string of characters that you as a human recognize as a data, but to the computer, '2016-02-14' has no more meaning than 'hereisyoursign'.