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:
- Key SSIS DateTime Functions
- SSIS DateTime Expression Examples for Common Problems
- Getting the Age in Years
- Convert SSIS DateTime to String and Format to YYYYMMDD
- Convert SSIS DateTime to String and Format to hh:mm:ss
- Convert SSIS DateTime to String and Format to YYYY-QQ
- Getting the Date and Time of Other Countries
- Check if the Current Year is a Leap Year
- Getting the Last Day of the Month
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.
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:
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:
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.
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 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())/365Assuming 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())/3652. 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:
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:
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:
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:
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:
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.
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:
The output is 29 because 2024 is a leap year. So, this works for leap years too.
