ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Expression Date Functions

Expression Date Functions

原创 Linux操作系统 作者:magicgao8888 时间:2009-04-17 10:17:52 0 删除 编辑


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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 准备购机
请登录后发表评论 登录
全部评论

注册时间:2009-03-10

  • 博文量
    35
  • 访问量
    72312