SQL Server Date Time - SSIS

Date and Time Functions

Date and time functions tend to cause confusion for new SSIS developers. In most instances, the different syntax is causing the difficulty. As mentioned earlier, the DatePart() function is a perfect example of this. T-SQL programmers need to double quote the date part portion of the function, or they will see an error similar to this:

The expression contains unrecognized token “dd”. If “dd” is a variable then it should be expressed as “@dd”. The specific token is not valid. If the token is intended to be a variable name, it should be prefixed with the @ symbol.

The fix is simple: put double quotation marks around the date part. A properly formatted DatePart() expression should look like this:

DATEPART( “dd”, GETDATE() )

Note that this expression returns the value of the day of the month — for example, 31 if the date is January 31, 2014. A common mistake is to expect this to be the day of the week. You can accomplish that task by changing the date part in the expression like this:

DATEPART( “dw”, GETDATE() )

These are just minor adjustments to the SSIS expression language, but they can create some frustration. Another example can be found when attempting to reference the date values in an expression. If you’re used to MS Access date literals, you may be tempted to use something like this:

“SELECT * FROM myTable WHERE myDate >= ” + #01/31/2014# (DOESN’T WORK IN SSIS)

That won’t work in SSIS; the # signs are used for a different purpose. If the string is going to be interpreted by SQL Server, just use the single quote around the date:

“SELECT * FROM MYTABLE WHERE MYDATE >= ‘” + “01/31/2014” + “‘”

If the string is just going to be printed, the single quotes aren’t needed. Alternatively, to plug in a date value from a variable, the expression would look like this:

“SELECT * FROM MYTABLE WHERE MYDATE >= ‘” +
(DT_WSTR, 255)@[System::ContainerStartTime] + “‘”

Notice that the value of the date variable must be cast to match the default Unicode data type for all expression strings of DT_WSTR. The problem with simply casting a date to a string is the fact that you get the entire date, which doesn’t translate into what you may want to use as a query parameter. This is clearer if the preceding expression is resolved:

Frequently Asked SSIS Interview Questions & Answers

SELECT * FROM MYTABLE WHERE MYDATE >= “02/22/2014 2:28:40 PM’

If your goal is truly to see results only from after 2:28:40 p.m., then this query will run as expected. If items from earlier in the day are also expected, then you need to do some work to parse out the values from the variable value. If the intent is just to return rows for the date that the package is running, it is much easier to create the expression like this (with your proper date style, of course):

“SELECT * FROM MYTABLE WHERE MYDATE >= CONVERT(nvarchar(10), getdate(), 101)”

This method allows SQL Server to do the work of substituting the current date from the server into the query predicate. However, if you need to parse a string from a date value in an expression, take apart one of the following formulas in this section to save you a bit of time:

This section covered most of the major syntactical issues that new users are likely to encounter with the expression language. The issues that have caused SSIS programmers the most trouble should not be a problem for you. Now you are ready to create some expressions and walk through the process of inserting them into SSIS packages to put them to work.

List of Related Microsoft Certification Courses: