Make SSIS DateTime Expressions Easy With DATEPART, DATEADD, and DATEDIFF

August 4th, 2022

SSIS DateTime expressions are mostly a part of daily ETL tasks. And the functions that come to mind are DATEPART, DATEADD, and DATEDIFF.

But what are the tricks to better use these functions?

That’s what we are going to cover. This article is divided into the following subtopics:

Rest assured the discussion will be plain and simple. And you will find real-world examples you can use right now.

Let’s begin.

Key SSIS DateTime Functions

What is SSIS DATEPART?

SSIS DATEPART returns a number representing a part of a date and time. Date and time include year, month, day, quarter, hour, minute, seconds, and more.

This is useful for extracting portions of transaction dates. And making it a report grouping.

Here’s the syntax:

DATEPART(<datepart>,<datetime expression>)

So, if you want to get the week number of the current date, use

DATEPART("week",GETDATE())

And if the current date is July 14, 2022, this will return 29. Check out the Expression Builder below. I used the one for the SSIS Variables.

SSIS DATEPART example returning the week number of the current date.

Mind the data type you used in the variable or this will return an error even if the expression syntax is correct.

Meanwhile, below are the following possible values for the <datepart> parameter:

Values you can use for the datepart argument of the DATEPART function.

You can use a more descriptive value like “year” or “month”. This is what we did in our example. You can also use abbreviations if you prefer to type less. It’s all up to you.

Note that you can use the above table for the 3 functions DATEPART, DATEADD, and DATEDIFF.

Then, the <datetime expression> can be a column with a DT_DBDATE or any SSIS DateTime type column. Or, it can also be a variable or a literal value. You can also pass another function that will return a DateTime value. Note that when you use a literal value like “07/14/2022” you need to cast it to the exact DateTime type you need. Here’s an example:

DATEPART("weekday",(DT_DBDATE) "07/14/2022")

If you don’t explicitly convert, a conversion error will occur, like the one below:

SSIS DateTime conversion error passing a date string to DATEPART.

What is SSIS DATEADD?

SSIS DATEADD lets you add a positive or negative number to a DateTime value with a specified date part. So, if you want to advance 10 days from the current date, use the date part “day” and add 10. But if you want to go back in time 10 days, use -10 instead.

Here’s the syntax to SSIS DATEADD:

DATEADD(<datepart>,<number to add>,<date expression>)

So, if you want to go back 10 days, here’s how you do it:

DATEADD("day",-10,GETDATE()

Check out the screenshot below.

Goinf back 10 days to the past using DATEADD.

What is SSIS DATEDIFF?

SSIS DATEDIFF lets you compute the difference between 2 dates and times. Below is the syntax:

DATEDIFF(<datepart>,<start date>, <end date>)

You can calculate elapsed days, times, and more with this function. It’s all up to the date part you will use. So, if you want to calculate how many months have passed between today and April 25, 2022, here’s the expression:

DATEDIFF(“month”, (DT_DBDATE) “04/25/2022”, GETDATE())

Again, you need to explicitly convert a literal value like the one above. Or you will get an error.

Here’s a screenshot of this example with the return value.

SSIS DateTime function DATEDIFF used to compute the difference in months between 2 dates.

SSIS DateTime Expression Examples for Common Problems

After discussing the 3 functions, you can use the following real-world examples. Note the example below uses literal values. But you can replace them with columns as applicable.

1. Getting the Age in Years

This should come in handy when you need to get the age of people or things. Here’s the expression using DATEDIFF:

DATEDIFF("day",(DT_DATE)"9/11/2004",GETDATE())/365

Assuming the current date is 7/14/2022, the result will be 17 years old. It’s correct because it’s not yet 9/11/2022. But if you use “year” as the date part, like this:

DATEDIFF("year",(DT_DATE)"9/11/2004",GETDATE())

the result is 18. That’s not right. It only considered the year part. And it’s the same with this:

YEAR(GETDATE()) - YEAR((DT_DATE)"9/11/2004")

You can still adjust to get the exact age with the decimal portion. But you must convert the result of DATEDIFF to DT_R8 or DT_DECIMAL. The result will be 17.8493150684932 if you change it like this:

(DT_R8)DATEDIFF("day",(DT_DATE)"9/11/2004",GETDATE())/365

2. Convert SSIS DateTime to String and Format to YYYYMMDD

This is a common SSIS task when you need to dump table values into a flat file like CSV. So, let’s form the expression using DATEPART. Here it is:

(DT_WSTR,4) DATEPART("year",GETDATE()) 
+ RIGHT("00"+ (DT_WSTR,2) DATEPART("month",GETDATE()),2)
+ RIGHT("00"+ (DT_WSTR,2) DATEPART("day",GETDATE()),2)
Let’s dissect the expression by starting with the year part. We start with the output type of (DT_WSTR, 4). The format YYYY needs a 4-character string. So, that’s why this is here. And remember that we need to concatenate this with the rest of the output. Meanwhile, the DATEPART(“year”, GETDATE()) is self-explanatory.
Then, we need a 2-digit month string. We need to pad a zero for single-digit months. So, it starts with RIGHT(“00”. And then we need to convert the output of DATEPART to a 2-digit month number. So, there’s the (DT_WSTR,2) again. Then, the DATEPART(“month”,GETDATE()) is self-explanatory. The final argument of 2 is for the RIGHT function. It limits the output to 2 characters.
Finally, there’s the day part. This is similar to the way we extract the month part. Here’s a screenshot from the Expression Builder:
SSIS DateTime expression for getting the YYYYMMDD string format from current date.

An alternative to this expression is using the YEAR, MONTH, and DAY functions. Check the expression below.

(DT_WSTR,4) YEAR(GETDATE())
+ RIGHT("00"+ (DT_WSTR,2) MONTH(GETDATE()),2)
+ RIGHT("00"+ (DT_WSTR,2) DAY(GETDATE()),2)

From these sample expressions, you can form a filename like sales-YYYYMMDD.CSV. Simply start with the “sales-” string. Then, append “.CSV” at the end of the string.

If you have a different format requirement like MMDDYYYY, simply rearrange the expression.

3. Convert SSIS DateTime to String and Format to hh:mm:ss

Here’s an example to extract the time part instead of the date.

RIGHT("00"+(DT_WSTR,2) DATEPART("hour",GETDATE()),2) +":" 
+ RIGHT("00"+ (DT_WSTR,2) DATEPART("minute",GETDATE()),2)+":"
+ RIGHT("00"+ (DT_WSTR,2) DATEPART("second",GETDATE()),2)

This is also like the way we extract the dates earlier. Here’s a screenshot from the Expression Builder:

SSIS DateTime expression to extract the time in the format hh:mm:ss

4. Convert SSIS DateTime to String and Format to YYYY-QQ

You may need a column for a year and quarter combination. This is useful for grouping datasets in reports. Here’s the expression:

(DT_WSTR,4) YEAR(GETDATE())  + "-"
+ RIGHT("00"+ (DT_WSTR,2) DATEPART("quarter",GETDATE()),2)

Simply replace the GETDATE() function with a date column. Here’s the screenshot:

Using YEAR and DATEPART functions to output YYYY-QQ

5. Getting the Date and Time of Other Countries

Here’s how to get the time from UTC/GMT +2 time zones like Prague, Czech Republic:

DATEADD("hour",2,GETUTCDATE())

And here’s the screenshot with the output:

Getting the time 2 hours from the Universal Time Coordinated.

Meanwhile, here’s the time in San Francisco, California, the USA concerning the current date.

DATEADD("hour",-15,GETDATE())

This assumes the time zone reference is GMT +8 like Manila, Philippines. Here’s the screenshot with the output:

Getting the date and time 15 hours from the current date and time.

6. Check if the Current Year is a Leap Year

Here’s the expression to check if the current year is a leap year:

(DATEPART("year",GETDATE()) %4==0) ? "Yes":"No"

Assuming the current date is 07/15/2022, the output will be No. Because 2022 is not a leap year.

We used a conditional operator (?) similar to the IIF function in Excel. Then, we get the year using DATEPART. And then compute with the modulus operator. Since leap years are every 4 years, 2022 won’t be a leap year. Because 2022 % 4 = 2, not zero.

7. Getting the Last Day of the Month

Here’s how to get the last day of the month. The possible values are 28,29,30, and 31. We used a date literal but you can always replace it with a column, variable, or parameter with a DateTime type.

DAY(DATEADD("day",-1,
            DATEADD("month",1,(DT_DBDATE)(
                         (DT_WSTR,4)YEAR((DT_DBDATE)"03/14/2022") + "-" 
                                   
                         +RIGHT((DT_WSTR,2)MONTH((DT_DBDATE)"03/14/2022"),2) 
                         + "-01"
                          )
                   )
           )
   )

To explain this stuff, let’s start with the innermost expression. We also formatted it to see the structure clearly.

The innermost part is extracting the year and month of the date in question. And we simply use 01 as the start day of the month. It outputs 2022-03-01.

Then, we use DATEADD to add 1 month to this (DATEADD(“month”,1,…). The output will be 2022-04-01.

Then, we go back 1 day from 2022-04-01 (DATEADD(“day”,-1,…). And the result is 2022-03-31.

And finally, we extract the day part of 2022-03-31. The result is 31 as shown below.

Getting the last day of the month. Using 03/14/2022, the output is 31.

This works even if the date in question belongs to a leap year.

But if your input is a string with a format of YYYYMM here’s the expression:

DAY(DATEADD("day",-1,
          DATEADD("month",1,(DT_DBDATE)(LEFT((DT_WSTR,6) "202402",4) + "-" +               
                                        SUBSTRING((DT_WSTR,6)"202402",5,2) + 
                                                       "-01")
                    )
           )
   )

The year and month in question are February 2024 or 202402. 

First, the innermost expression is to form 2024-02-01. It uses LEFT to extract the 4-digit year. And SUBSTRING to extract the 2-digit month starting from the 5th character position. Then, simply append -01 for the day part.

Then, add 1 month to 2024-02-01 (DATEADD(“month”,1,..). The result is 2024-03-01.

Then, go back 1 day from 2024-03-01 (DATEADD(“day”,-1,…). The result is 2024-02-29.

Finally, extract the day from 2024-02-29. The result is 29 as seen below:

Getting the last day of the month with a YYYYMM string as input.

The output is 29 because 2024 is a leap year. So, this works for leap years too.

Comments are closed.