Oracle - Get Current Date Formatted

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

Solution 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'.

Solution 2:

you may change the variable NLS_DATE_FORMAT.

ALTER SESSION SET NLS_DATE_FORMAT ='DD/MM/YYYY';

see also http://www.dba-oracle.com/t_nls_date_format.htm