I am trying to put the following as an expression in the SSIS Derived Column Transformation Editor. DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) It is not allowing it. This works fine in a regular SQL statement. Does anyone know how I can get this to work?

Why not just use "getdate()"?You aren't adding or subtracting anything in that statement.

See this for help:http://technet.microsoft.com/en-us/library/ms141719.aspxhttp://technet.microsoft.com/en-us/library/ms139927.aspx

I am trying to remove the time component from my date, so that is why I can't just use GetDate().

c9jad wrote: I am trying to remove the time component from my date, so that is why I can't just use GetDate().Use the following expression. (DT_WSTR,30)(DT_DBDATE)getdate()

DATEADD and DATEDIFF function are quite a bit different in SSIS. Try this : Code SnippetDATEADD("d", DATEDIFF("d", 0, GETDATE()), 0) The keyword DAY is not understood by SSIS. Jean-Pierre Riehl

Thank you Phil. This works great for one thing I am doing. For another thing I will be doing I am going to need to subtract 1 from GetDate. I tried doing that with the expression you provided, but it did not like it.

c9jad wrote: Thank you Phil. This works great for one thing I am doing. For another thing I will be doing I am going to need to subtract 1 from GetDate. I tried doing that with the expression you provided, but it did not like it. (DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate()))

Phil, Thank you very much for you help. I appreciate it. Jim

HiI am trying to use a derived column and input todays date as 20090416, could anyone be so kind as to tell me the code to put in?I apologise in advance to being a complete novice!Many Thanks

Sure thing, that is a commonly requested expression (You can replace system start time with get date below) (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) Please mark answered posts. Thanks for your time.

c9jad wrote: Thank you Phil. This works great for one thing I am doing. For another thing I will be doing I am going to need to subtract 1 from GetDate. I tried doing that with the expression you provided, but it did not like it. (DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate())) Phil, I have a formating question. I just want to be able to get today's date with no time. I seen where your response does it but I get thistoday is Feb 10, 2010with this (DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate()))I get2010-02-10but I rather get 02-10-2010 is there away to format it in the expressions In TSQL I use this statement to get the date with time set to midnight select DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0) but SSIS doesn't like the 0 for the begining of time date and doesn't like the interger value returned by datediff

c9jad wrote: Thank you Phil. This works great for one thing I am doing. For another thing I will be doing I am going to need to subtract 1 from GetDate. I tried doing that with the expression you provided, but it did not like it. (DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate())) Phil, I have a formating question. I just want to be able to get today's date with no time. I seen where your response does it but I get thistoday is Feb 10, 2010with this (DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate()))I get2010-02-10but I rather get 02-10-2010 is there away to format it in the expressions In TSQL I use this statement to get the date with time set to midnight select DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0) but SSIS doesn't like the 0 for the begining of time date and doesn't like the interger value returned by datediff I got this to workDATEADD("Day", -1,(DT_DBDATE)DATEDIFF("Day", (DT_DBDATE) 0, GETDATE()))which set the value of a user defined var using the expression builder

Hey, I stumbled across this posting when I was trying to figure out how to enter a LoadDate of today (no timestamp). I ended up added a derived column to my data flow task with the following syntax: (DT_DBDATE)GETDATE() And I set the datatype format to database timestamp. Worked like a charm! Elizabeth Priddy