Using Derived Column to create a date value in the format YYYY-MM-01 00:00:00.000 for each row

Question

Im having a problem with derived columns in SSIS. When in SSMS i can set a column to have a default value using the following code:

(dateadd(month,datediff(month,(0),getdate())-(1),(0)))

and when data is entered into the database it will give it the timestamp of the previous month in the following format for example:

 2010-09-01 00:00:00.000

This strangely is what im looking for and trying to duplicate/produce similar using the Derived Column DFT.

So far i have:

DATEADD("mm",DATEDIFF("mm",GETDATE(),GETDATE()) - 1,GETDATE())

which produces the month succesfully but the GETDATE() is not a correct replacement for the 0's in the original code.

Does the 0's in the original code signify the start date in SQL?

Any help would be much appreciated.

Regards,

Lee

Answer 1

your first code fragment is "flooring" the datetime to the month (making the date the 1st of the given month with no time) the "-1" makes it the previous month. All the extra unnecessary parenthesis in this code fragment give me a headache, here is the equivalent:

dateadd(month,datediff(month,0,getdate())-1,0)

This is how to floor a datetime to different units:

--Floor a datetime
DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';

SELECT '0 None',  @datetime                                                                   -- none    2008-09-17 12:56:53.430
UNION SELECT '1 Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01')  -- Second: 2008-09-17 12:56:53.000
UNION SELECT '2 Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0)                        -- Minute: 2008-09-17 12:56:00.000
UNION SELECT '3 Hour',  DATEADD(hour,DATEDIFF(hour,0,@datetime),0)                            -- Hour:   2008-09-17 12:00:00.000
UNION SELECT '4 Day',   DATEADD(day,DATEDIFF(day,0,@datetime),0)                              -- Day:    2008-09-17 00:00:00.000
UNION SELECT '5 Month', DATEADD(month,DATEDIFF(month,0,@datetime),0)                          -- Month:  2008-09-01 00:00:00.000
UNION SELECT '6 Year',  DATEADD(year,DATEDIFF(year,0,@datetime),0)                            -- Year:   2008-01-01 00:00:00.000
ORDER BY 1
PRINT' '
PRINT 'Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor'
PRINT 'this always uses a date less than the given date, so there will be no arithmetic overflow'
SELECT '1 Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1)  -- Second: 2008-09-17 12:56:53.000

the second code fragment will not properly floor the datetime to the month, it will only move the date to the previous month and use the same day and time as the given datetime.

Beyond that I'm not sure what you are really asking.

here is a breakdown of what is happening in the OP's first code fragment:

select convert(datetime,0),'first datetime "0"'
select datediff(month,0,getdate()), 'months difference between the first datetime (1900-01-01) and given "getdate()"'
select datediff(month,0,getdate())-1, 'months difference between the first datetime (1900-01-01) and month previous to given "getdate()"'
select dateadd(month,datediff(month,0,getdate())-1,0), 'takes the first datetime (1900-01-01) and adds 1328 months onto that'

OUTPUT:

----------------------- ------------------
1900-01-01 00:00:00.000 first datetime "0"

----------- --------------------------------------------------------------------------------
1329        months difference between the first datetime (1900-01-01) and given "getdate()"

----------- --------------------------------------------------------------------------------------------------
1328        months difference between the first datetime (1900-01-01) and month previous to given "getdate()"

----------------------- --------------------------------------------------------------------
2010-09-01 00:00:00.000 takes the first datetime (1900-01-01) and adds 1328 months onto that

Answer 2

month,0 makes it round down to the beginning of the month.

Can't you use your first expression if it does what you want? Or use the second expression but switch the two GETDATE()s you've added to 0. Do you get an error if you try that?

Answer 3

Here's what I think you may be looking for. It is an SSIS expression that gets the first day of the previous month for a given day (GETDATE() in the example).

DATEADD("mm",DATEDIFF("mm", (DT_DBTIMESTAMP)2, GETDATE()) - 1, (DT_DBTIMESTAMP)2)

I tried to simulate your SQL version of the expression, which determined the number of months between the 0 datetime and the current datetime. And, then it added the number of months to the 0 datetime.

It's not too important what the 0 datetime is, except that you wanted the 1st day of the month. In SQL the 0 datetime is 1900-01-01 00:00:00.000, so adding months automatically gives you the first day of the month. In SSIS expressions, the 0 datetime is 1899-12-30 00:00:00.000. Since you want the first day of a month, the expression above refers to the 2 datetime. So, in the expression (DT_DBTIMESTAMP)2 casts the number 2 to 1900-01-01 00:00:00.000.

Answer 4

I think what you need in your derived column is this synatx:

DATEADD("mm",-1,(DT_DBTIMESTAMP)((DT_WSTR,4)DATEPART("YYYY",GETDATE()) + "-" + (DT_WSTR,2)DATEPART("mm",GETDATE()) + "-01"))