Expression Date Functions

magicgao8888發表於2009-04-17
ExpressFunction

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 faimilarity is very helpfull, 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14321372/viewspace-590736/，如需轉載，請註明出處，否則將追究法律責任。

相關文章