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
No comments:
Post a Comment