December 3, 2012 at 7:48 am
Hi All,
I wanted to find the starting of the week and poulate it as an additional column. Im trying to use dervived column in SSIS
SQL Equivalent is:
SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)
2012-10-29 00:00:00.000 - Fine
Im not able to use the same sql statement in SSIS dervived col. I tried using the below expression:
DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0).... --- But this returns diff results
03/11/2012 00:00:00. ---It returns the subsequent saturday and not the staring day of the week i.e Monday. Any help on this?
Thanks!
December 3, 2012 at 8:55 am
SELECT CAST(0 AS DATETIME)
Resolves to
1900-01-01 00:00:00.000
(DT_DBDATE)0
Resolves to
1899-12-30
There's your two days difference
December 3, 2012 at 9:09 am
Thanks for your reply..
I tired using all the datetime expressions related to date with the below exprseeion in dervived column
DATEADD("WK",DATEDIFF("WK",0,date),0)- but the same query gives correct answers in sql , but shows error in SSIS dervived expressions.
I get results this way :-
Date Available in db Result Expected Result
01/11/2012 01:28 03/11/2012 00:00 29/10/2012 00:00
04/11/2012 19:12 10/11/2012 00:00 03/11/2012 00:00
06/11/2012 09:34 10/11/2012 00:00 03/11/2012 00:00
12/11/2012 10:59 17/11/2012 00:00 10/11/2012 00:00
How to achieve the expected results using dervived column in SSIS- every date available in DB, should show the starting day - Monday of that particular week.
Thanks!
Expected result-(29/10,03/11,10/11 are the starting of the week-Monday)
December 3, 2012 at 9:33 am
var05 (12/3/2012)
Hi All,I wanted to find the starting of the week and poulate it as an additional column. Im trying to use dervived column in SSIS
SQL Equivalent is:
SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)
2012-10-29 00:00:00.000 - Fine
Im not able to use the same sql statement in SSIS dervived col. I tried using the below expression:
DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0).... --- But this returns diff results
03/11/2012 00:00:00. ---It returns the subsequent saturday and not the staring day of the week i.e Monday. Any help on this?
Thanks!
Change this:
DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0)....
To this:
DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)"1900-01-01",'11/01/2012'),(DT_DBDATE)"1900-01-01")....
Let us know if this works.
December 3, 2012 at 9:10 pm
As Ray indicated earlier, (DT_DBDATE)0 evaluates to 1899-12-30. This would be equivalent to using -2 inplace of 0 in the T-SQL calls as shown here:
/*
Date Available in db Result Expected Result
01/11/2012 01:28 03/11/2012 00:00 29/10/2012 00:00
04/11/2012 19:12 10/11/2012 00:00 03/11/2012 00:00
06/11/2012 09:34 10/11/2012 00:00 03/11/2012 00:00
12/11/2012 10:59 17/11/2012 00:00 10/11/2012 00:00
*/
WITH TestData AS (
SELECT
cast(TestDate as datetime) TestDate
FROM
(VALUES ('20121101 01:28'),('20121104 19:12'),('20121106 09:34'),('20121112 10:59'))dt(TestDate)
)
select
TestDate,
dateadd(wk,datediff(wk,0,TestDate),0),
dateadd(wk,datediff(wk,-2,TestDate),-2)
from
TestData;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply