Getting last day of a month from a given date !

• SQLFanatic

  SSCommitted

  Points: 1640

  More actions

  June 22, 2010 at 1:40 pm

  #143832

  Hi,

  I am trying to derive the last day of the month from the given value through SSIS expression. I am able to do this through T-SQL but get different result in SSIS.

  Any help is greatly appeciated. Thanks.

  Here's the T-SQL expression that yeilds what I need:

  i.e. 2009-03-31 00:00:00.000

  DECLARE @date AS VARCHAR(10)

  SET @date = '2009/03'

  SELECT DATEADD(day,-1,DATEADD(month,DATEDIFF(month,0,(@date + '/01'))+1,0))

  AS [RequiredDate]

  Result: 2009-03-31 00:00:00.000

  The SSIS Expression that I'm using:

  DATEADD("day",-1,DATEADD("month",DATEDIFF("month",(DT_DBDATE)0,((DT_DBDATE)(<ColumnName> + "/01")))+1,(DT_DBDATE)0))

  But, the result I am getting is:

  2009/067/29/2009 12:00:00 AM

  2009/067/29/2009 12:00:00 AM

  2009/045/29/2009 12:00:00 AM

  2009/045/29/2009 12:00:00 AM

  ________________________________________________________________
  "The greatest ignorance is being proud of your learning"

• gtschech

  Ten Centuries

  Points: 1351

  More actions

  June 23, 2010 at 12:42 am

  #1183737

  Hi,

  your problems aris from the diff with the 0 date. If you use this, dateadd(month) will always add 30 days...

  it seems that this one

  DATEADD("day",-1,DATEADD("month",1,((DT_DBDATE)(DateStrimg + "/01"))))

  would do the job...

  WM_JUSTMY2CENTS