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

Case Statement Fails Converting Varchar to Number Even Using isnumeric

Short Description:  A column containing only numbers and empty strings fails to convert using isnumeric as a condition in a case statement.  It returns Msg 8114, "Error converting data type varchar to numeric."

Example:
This statement should only attempt to convert strings that can be converted to numbers

select
   case
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable

However it fails with the error above.

Solution:
Isnumeric appears to evaluate an empty string as numeric, but convert fails attempting the actual conversion.  To make the case statement work, add a condition to detect the empty string and do something with it (in this case make it null).

select
   case
 when StringValue = '' then null
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable

Using a Date Stamped File as a File Connection DataSource in SSIS

Quick Description:  How to set up an SSIS job to look for a file stamped with today's (or another day's) date. Also covering the difference between leading 0s/two digit day and month in SSIS expression date strings.

How To:  Create a flat file connection, initially hardcode the location of a sample file so that you can set datatypes, set the file connection's 'Connection String' property to a variable, populate the variable with an expression that pulls date parts out of the date you're looking for.

• In a Data Flow task, create a Flat File Connection by dragging a Flat File Source into the Connection Manager pane.
• Create a variable to hold the file name.  Right click the surface of the Data Flow task, and select Variables.
• Open the Flat File connection, and in the File Name box, enter the name of a sample file and perform general setup of fields and datatypes
• Create a variable of type string.
• Close the dialog box, highlight the connection manager for the file in question, and go to the properties pane.
• In the properties pane, select Expressions.
• Select your variable and close the dialog.
• To populate the variable with the correct file name, break down which date parts are in what order (year, month, day, hour, minute, second) and create the expression using the date parts as below.
• Create a variable to hold the file name.  Right click the surface of the Data Flow task, and select Variables.

Below are examples for leading 0 and no leading 0 (always two digits regardless of whether the number is 10, vs. only one digit when under 10)

Assuming today's date is 2015-06-10 12:39
If you want the leading 0

"D:\\myFilePath\\" +
Right("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4) +
Right("0" + (DT_STR,4,1252) DatePart("mm",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("dd",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("mi",getdate()),2) +
"_MyFileName.txt"

Evaluates to:   D:\myFilePath\201506101239_MyFileName.txt


If you don't want leading 0s

"D:\\myFilePath\\" +
(DT_WSTR, 4)(DATEPART("yyyy", GETDATE())) +
(DT_WSTR, 4)(DATEPART("mm", GETDATE()))+
(DT_WSTR, 4)(DATEPART("dd", GETDATE()))+
(DT_WSTR, 4)(DATEPART("hh", GETDATE())) +
(DT_WSTR, 4)(DATEPART("mi", GETDATE()))+
 "_MyFileName.txt"

This evaluates to:  D:\myFilePath\20156101239_MyFileName.txt