|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Feb
21
Written by:
StevenFeuersteinTW
Tuesday, February 21, 2012 10:50 AM
Consider the following block:
DECLARE
l_date DATE;
BEGIN
l_date := '15-FEB-2011';
END;
I declare a date variable and then assign a string to it. Will this block raise an error? Hard to say. It depends on the NLS settings for the default date format – for the database as a whole or for my session. If this string doesn’t match the default, then Oracle will raise an error, such as:
ORA-01858: a non-numeric character was found where a numeric was expected
This block also performs an implicit conversion, which is generally frowned upon. We are encouraged, instead, to explicitly convert from string to date as in:
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('15-FEB-2011');
END;
Yes, that is better – but still will raise the same error. To avoid making an assumption about the default date format, many of us have provided that format in the call to TO_DATE, as in:
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('FEB-15-2011', 'MON-DD-YYYY');
END;
Then, as long as the string matches the format, Oracle will not raise an error, regardless of the default date format.
But oh that is a pain in the you know what to write, over and over again. So Oracle now (actually, since Oracle9!) supports the ANSI date literal, which means that you can assign a value to a date as follows:
DECLARE
l_date DATE;
BEGIN
l_date := DATE '2011-02-15';
END;
In other words: the keyword DATE follows by a literal string in the form ‘YYYY-MM-DD’. You have no choice in this format; it cannot be changed by changing NLS settings. In addition, the ANSI date literal contains no time portion. So if you need to specify a time other than midnight for your date variable, you will still need to fall back on code like this:
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('FEB-15-2011 13:14:15'
, 'MON-DD-YYYY HH24:MI:SS');
END;
But it is certainly cleaner and simpler code when you only need to assign a hard-coded literal value to a date variable or column in the database.
As I noted above, support for the ANSI date literal has been in place for quite a while, but very few Oracle technologists are aware of this feature.
4 comment(s) so far...
Re: The Date Literal
Hi Steven
A few comments:
1. If you need to specify time part for a DATE variable/parameter etc you use the TIMESTAMP literal and Oracle implicitly converts this TIMESTAMP to your DATE, truncating any fractional seconds (no NLS involved in this conversion).
2. DATE and TIMESTAMP literals were introduced for SQL in 8i Release 1 but couldn't be used in PL/SQL.
3. DATE literals were introduced for PL/SQL in 8i Release 2.
4. TIMESTAMP literals were introduced for PL/SQL in 9i Release 1.
5. I believe DATE and TIMESTAMP literals were poorly documented until 9i Release 1.
Consider the following expanded version of your example:
prompt PL/SQL DATE without time
DECLARE
l_date DATE;
BEGIN
l_date := DATE '2011-02-15';
END;
/
prompt PL/SQL DATE with time
DECLARE
l_date DATE;
BEGIN
l_date := TIMESTAMP '2011-02-15 13:14:15';
END;
/
prompt PL/SQL DATE with fractional time
DECLARE
l_date DATE;
BEGIN
l_date := TIMESTAMP '2011-02-15 13:14:15.123456789';
dbms_output.put_line(to_char(l_date, 'yyyy-mm-dd hh24:mi:ss'));
l_date := TIMESTAMP '2011-02-15 13:14:15.567890';
dbms_output.put_line(to_char(l_date, 'yyyy-mm-dd hh24:mi:ss'));
END;
/
prompt PL/SQL TIMESTAMP with fractional time
DECLARE
l_timestamp TIMESTAMP;
BEGIN
l_timestamp := TIMESTAMP '2011-02-15 13:14:15.12345679';
END;
/
prompt SQL DATE without time
SELECT DATE '2011-02-15'
FROM dual;
prompt SQL DATE with time
SELECT TIMESTAMP '2011-02-15 13:14:15'
FROM dual;
prompt SQL TIMESTAMP with fractional time
SELECT TIMESTAMP '2011-02-15 13:14:15.12345679'
FROM dual;
Cheers
Finn
_____________________________________________________
Finn Ellebaek Nielsen | Oracle Test Coach | oracletesting.com
By FinnEllebaekNielsen on
Wednesday, February 22, 2012 1:52 AM
|
Re: The Date Literal
It's also worth noting that "TO_DATE ('FEB-15-2011', 'MON-DD-YYYY')" could also fail, as it assumes your nls_date_language is English. Changing the date language to something else will mean it fails:
SQL> alter session set nls_date_language=french;
Session altered.
SQL> select TO_DATE ('FEB-15-2011', 'MON-DD-YYYY') from dual;
select TO_DATE ('FEB-15-2011', 'MON-DD-YYYY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Thankfully, there's a third parameter that you can add:
SQL> select TO_DATE ('FEB-15-2011', 'MON-DD-YYYY', 'nls_date_language=english') from dual;
TO_DATE('FE
-----------
15/FÉVR./11
Or, you could just avoid the issue by using numbers:
SQL> select to_date('15/02/2011', 'dd/mm/yyyy') from dual;
TO_DATE('15
-----------
15/FÉVR./11
By Boneist1 on
Wednesday, February 22, 2012 2:34 AM
|
Re: The Date Literal
Shameless plug to a quiz on Stevens PL/SQL challenge website on this topic:
plsqlchallenge.com/pls/apex/f?p=10000:651:5167318539832187::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:7506,8286&cs=17B76B099643E5D4DEA2E87C38EDD1255
:-)
By kibeha on
Thursday, February 23, 2012 6:00 AM
|
Re: The Date Literal
Wow! Never knew about this. Just today I ran across the following example:
extract(YEAR FROM DATE '2003-08-22')
and I couldn't figure out what was going on. I tried extract(YEAR FROM DATE sysdate) and kept getting an error ...
Then I log on to your blog and there is the answer. Thank you!
By sacgal on
Thursday, February 23, 2012 12:47 PM
|
|
|