Oracle – Easy Date Strings (and NOT)

Whenever I type in a date strings in Oracle, I convert the strings to Date data type in my code as well as my queries without relying on the session setting for date format. I will explain why.

Session date format setting:

Session date format settings govern how date strings should be converted into dates (the format). Let us see an example of how to set it and use it.

Instead of converting using TO_DATE or another mechanism, I could have done

ALTER SESSION
SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

and from this time forward I could use regular date strings without a format (notice no explicit conversion of character string to DATE data type)

SELECT COUNT(1)
FROM dba_objects
WHERE created > '2014-09-10 00:00:00'

This is very convenient but very bad!

Why is the above bad?

Let us say that your procedure had code such as the above. Someone who has his session settings set to

ALTER SESSION
SET NLS_DATE_FORMAT='yyyy-dd-mm hh24:mi:ss';

would get different results when running your procedure that has this statement

SELECT COUNT(1)
FROM dba_objects
WHERE created > '2014-09-10 00:00:00'

Why? Notice that he changed ‘yyyy-mm-dd hh24:mi:ss’ to ‘yyyy-dd-mm hh24:mi:ss’ because his country prefers to put the day ahead of the month as a general rule. In fact, I have seen production code produce different results because it relied on the date-format to be a certain way!

Checking your default NLS_DATE_FORMAT

By the way, if you wanted to see what the default nls_date_format is for your session if you had not set anything, you could run

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

In my case, I got

DD-MON-RR

Code that always works

If you had taken the time and effort to always convert date strings into date data types using explicit conversion, your code is safe, irrespective of the session level setting because you are not relying on magic strings anymore but rather dealing with the actual date data type at that point.

SELECT COUNT(1)
FROM dba_objects
WHERE created > TO_DATE('20140101 00:00:00','YYYYMMDD HH24:MI:SS')

Notice how I specifically state the format of the date and convert the string to a Date data type.

If I did not care about the time component, I could simply say:

SELECT COUNT(1)
FROM dba_objects
WHERE created > TO_DATE('20140101 ','YYYYMMDD')

..but, that was tedious

If you do a lot of free-hand, adhoc queries using dates, it gets tedious after a while. One day I came across Tanel Poder’s blog post on doing this the lazy way:

For example, to get all the objects created after the year 2013, I could say

SELECT COUNT(1)
FROM dba_objects
WHERE created > DATE'2013-12-31'

Notice how the string is converted to a date using the “date” prefix to the string. When using it this way, the format is always YYYY-MM-DD irrespective of everything else.

You can also do something similar for timestamp data type since that is even harder to work with when converting from string format

SELECT COUNT(1)
FROM dba_objects
WHERE created > TIMESTAMP'2013-12-31 23:59:59';

Warning:

The reason for this post is because I have seen developers set a specific format at the beginning of a batch job and assume the format inside the procedures and functions. i.e., the procedures and functions will not do any explicit conversion using a format and just be a string that assumes the NLS format. However, someone who has no knowledge of the NLS_DATE_FORMAT initialization in the batch job might run it another way outside the batch job and get potentially different results.

I am only suggesting the method in this blog post for free-hand adhoc queries and not production code as there seems to be performance implications (see the complete article on Tanel Poder’s blog including the comments section). Also, it helps to not use it in production code because we are looking out for that guy who assumes that the default date format when using the lazy conversion is really his country’s format and it may not be!

As a general rule, when you have a date string, always convert it to the DATE type explicitly using the appropriate format string.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s