July 27, 2009 at 10:44 am
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.
July 27, 2009 at 1:17 pm
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")
July 27, 2009 at 1:33 pm
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
July 28, 2009 at 7:38 am
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
July 28, 2009 at 4:25 pm
Thanks to all of you for the response.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply