Expression Date Functions

by Darren Green 28 Oct 2008 07:58

Date Parts

Expressions support a range of date related functions such as DATEADD, with the same basic syntax to that found in T-SQL. Whilst the familiarity is very helpful, the difference that catches me out is the format of date part which must be quoted.

T-SQL allows this:

 DATEADD(n, -10, GETDATE())
 DATEADD(mi, -10, GETDATE())
 DATEADD(minute, -10, GETDATE())

The SSIS equivalent is:

 DATEADD("n", -10, GETDATE())
 DATEADD("mi", -10, GETDATE())
 DATEADD("minute", -10, GETDATE())

Related functions that use the same date part tokens -

  • DATEADD
  • DATEDIFF
  • DATEPART

Month Name Expressions

Here are some month name expressions, just waiting for a DATENAME function.

Get the month name, for the column RowDate:

 (MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : 
  MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : 
  MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : 
  MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : 
  MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

Get formatted month and year, mmm (yyyy), from the column RowDate:

 (MONTH(RowDate) == 1 ? "Jan" : MONTH(RowDate) == 2 ? "Feb" : MONTH(RowDate) == 3 ? "Mar" : 
  MONTH(RowDate) == 4 ? "Apr" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "Jun" : 
  MONTH(RowDate) == 7 ? "Jul" : MONTH(RowDate) == 8 ? "Aug" : MONTH(RowDate) == 9 ? "Sep" : 
  MONTH(RowDate) == 10 ? "Oct" : MONTH(RowDate) == 11 ? "Nov" : MONTH(RowDate) == 12 ? "Dec" : 
  "ERR") + " (" + (DT_WSTR,4)YEAR(RowDate) + ")"

yyyy-mm-dd

The common yyyy-mm-dd format is often used in file names, for example:

 C:\Temp\ErrorCodes\2005-11-18.txt

A sample expression to achieve this is:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"

A similar expression, but this time deriving the file name based on yesterday’s date, useful for loading the previous day's data:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"

yyyy-mm-dd hh:nn:ss

Another simple time and date expression example:

 2006-06-22 11:48:52
 (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

Or alternatively:

 (DT_WSTR, 10) (DT_DBDATE) GETDATE()  + " " + (DT_WSTR, 8) (DT_DBTIME) GETDATE()

dd-mm-yyyy

 RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + (DT_WSTR,4)YEAR(GETDATE())
 18-07-2006

yyyymmdd

A simple yyyymmdd formatted string from a DateTime type variable

 (DT_WSTR,4)YEAR(@[User::DateTimeVar])
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)

An alternative yyyymmdd formatted string from a DateTime type variable.

 (DT_WSTR,8) (
   (YEAR(@[User::DateTimeVar]) * 10000) + 
    (MONTH(@[User::DateTimeVar]) * 100) + 
    DAY(@[User::DateTimeVar])
    )

yyyymmdd hh:nn:ss.mi

 
 (DT_WSTR,8) (
    (YEAR(@[User::MaxStartDate]) * 10000) + 
      (MONTH(@[User::MaxStartDate]) * 100) + 
      DAY(@[User::MaxStartDate]) 
    ) + " " + 
    RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[User::MaxStartDate]), 2) + "."
 + (DT_WSTR,3)DATEPART("Ms", @[User::MaxStartDate])
 20070511 09:40:38.123

ISDATE() workaround for date values

This checks against a string value where the source system used "00/00/00" as a lack of date. There were also columns that were out of the normal range for date values (AD 1, 0600, etc). This expression NULLs those values out. I figured that someone may have been struggling with this and the lack of an ISDATE function for expressions. It could be easily adapted to handle other date formats and checking for out of range dates.

 (Date_To_Check == "00/00/00" || (DT_Date)Date_To_Check < (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check 

Get Date - Remove Time

If you wish to return the date only, so setting the time to 00:00 you can easily do this by casting to DT_DBDATE. The data type has limited support, so casting it back to a DT_DATE will allow you to use it more readily.

 (DT_DATE)(DT_DBDATE)@[User::WorkingDate]

Or, alternatively:

 DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)

Calculate the Beginning of a Previous Month

This expression starts from today, moves back three months (as an example), subtracts the day-count from the current day-of-month to get the first day, then converts the expression to a DT_DBDATE type (which does not support a time component) then converts it back to a regular DT_DATE, which does have a time component - but now it's truncated the time to 00:00 AM.

 (DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))

Calculate the End of a Previous Month

To get Midnight on the last day of the previous month, we back up to the first day of the immediately following month, truncate the time to 00:00 (as in the previous example) and then subtract 1 minute to get the ending time of the previous day. (Note, in SQL Server 2005 SP2, subtracting 3ms to get the absolute last time-slice of the previous day did not work properly with the MONTH() function.)

 DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))

Getting the Fiscal Year for a Date

To get the fiscal year for a given date, use the conditional operator to check the month part of the date, and return either the year part of the date, or the year part of the date plus one depending on the cutoff of the fiscal year definition. This sample assumes a fiscal year that ends June 30th:

 MONTH( @[User::InputDate]  ) <= 6 ? YEAR ( @[User::InputDate]  )  : YEAR ( @[User::InputDate]  )  + 1

Comments (4) -

5/26/2010 4:39:16 PM #

Darren Comeau

A very helpful article.  I have something to contribute for solving a problem of adding two values together one being a date and the other a time but in this case both are stored as timestamps with Midnight or 1st Jan where data is missing.

I solved this by converting date to dbdate, time to dbtime, converting both to a string which can be concatenated and finally converting the string back to a date.

The expression being (DT_DATE)((DT_STR,20,1252)((DT_DBDATE)date) + " " + (DT_STR,20,1252)((DT_DBTIME)time))

date 2010-05-21 00:00:00.000
time 2010-01-01 00:01:10.000
dbdate 21/05/2010
dbtime 00:01:10

date 2010-05-21 00:01:10.0000000

Darren Comeau United Kingdom

6/30/2010 7:01:41 PM #

Eric

Note: the colons are not vaild in a file name.

Eric United States

9/8/2010 12:53:01 AM #

Gyhant

Thanks for very interesting tips ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly e.g. for today 09/07/2010 it will correctly evaluate to 09/06/2010 but not when the current month goes into the next month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

Gyhant Canada

10/1/2010 9:24:04 PM #

Isabella

Thank you very much!!!!
You helped to delete yesterday files (as first step) when starting to run job today.

Isabella United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS