Thursday, June 25, 2015

SQL DatePart function equivalents for Oracle

Quick Description:  Quick example and where to find the documentation on Oracle's equivalents for DatePart and convert(datepart, datefield).




Looking for equivalents to SQL's DatePart or Convert (and then DatePart) for converting a string to a date part, I was pleasantly surprised to see that the functionality does, in fact, exist in Oracle/PL/SQL, and is relatively straightforward.  I was able to use it without messing with the NLS_DATE_FORMAT.  It has a funky syntax and is sometimes looks a lot like it belongs in a function (I think it technically is a function), but it's actually a reasonable substitute for the most basic portions of DatePart and using Convert to get a datepart from a string.  I haven't tried it across large amounts of data, but the claim is that it's written with performance in mind.  The method for a string even has less moving parts.


Kudos to whoever wrote the article at Oracle, it's very clear.  I like the little UML diagram.




A couple of examples:


To get a date part from a field that is already in the date datatype:
For the (relatively) equivalent functionality of 
SELECT DATEPART(month, MYFIELD) (Returns the month of each value in the field)
Use the syntax below to get a date part from a field that is already in the date datatype

SELECT EXTRACT(YEAR FROM MYFIELD;)
FROM
         Returns the year of each value in the field 




To get a date part from a string that (in SQL) might need to be converted first, both can be done in one statement, but the Oracle one is cleaner.
For the (relatively) equivalent functionality of 


SELECT DATEPART(MONTH, CONVERT(DATE, '2015-06-25'))
Returns 6


Use the syntax below to get a date part from a field that needs to be converted to date


SELECT EXTRACT(MONTH FROM DATE '2015-06-25') FROM DUAL;
Returns 6

No comments:

Post a Comment