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:2803/11/2012 00:0029/10/2012 00:00
04/11/2012 19:1210/11/2012 00:0003/11/2012 00:00
06/11/2012 09:3410/11/2012 00:0003/11/2012 00:00
12/11/2012 10:5917/11/2012 00:0010/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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy