Expressions in SSIS - help needed with datetime

• touchmeknot123

  Right there with Babe

  Points: 735

  More actions

  July 27, 2009 at 10:44 am

  #137023

  Hi,

  I have to convert the following to expressions in SSIS (derived column transformation).

  Please help.

  declare @date1 datetime,@date2 datetime

  set @date1='2009-07-18 15:46:56.050'

  if (datepart(dd,@date1) < 15 )
  select @date1
  else
  select CAST(CAST(YEAR(@date1) AS VARCHAR(4)) + '-' + CAST(MONTH(dateadd(mm,1,@date1)) AS VARCHAR(2)) + '-01' AS DATETIME)

  Thanks in advance.

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  July 27, 2009 at 1:17 pm

  #1030706

  The input column is assumed to be a DB_DBTIMESTAMP.

  DATEPART("Day", MyDate ) < 15 ? MyDate : (DT_DBTIMESTAMP) ( (DT_STR,4,1252) DATEPART( "Year", MyDate ) + "-" + (DT_STR,2,1252) DATEPART( "Month", MyDate ) + "-01")

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  July 27, 2009 at 1:33 pm

  #1030714

  You need to use the IIF(<Logical Expression>, <True Part>, <False Part>).

  Also, I think that your false part can be greatly simplified by using the DateAdd() function. Try the following instead of all those CASTS. DateAdd(

  "Month"

  , 1

  , DateAdd("Day", 1-Day(@date1), @date1) -- First day of current month

  )

  The problem is that there is no easy way to calculate the first day of the next month, but I think that this is clearer than using all of those casts.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 28, 2009 at 7:38 am

  #1031011

  Here's another option, similar to what Drew has suggested:

  DAY(date) < 15 ? DATEADD("Month",DATEDIFF("Month",(DT_DBTIMESTAMP)"1/1/1900",date),(DT_DBTIMESTAMP)"1/1/1900") : date

  This is based on the T-SQL date functions posted by Lynn Pettis on his blog. I had to change the 0's to "1/1/1900" as .NET won't let you use the 0 for a date like T-SQL will.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• touchmeknot123

  Right there with Babe

  Points: 735

  More actions

  July 28, 2009 at 4:25 pm

  #1031388

  Thanks to all of you for the response.