Calculate last friday date with timestamp ssis
-
hello,
i want to calculate last friday’s date in ssis with timestamp. Below gives me only date. Can u help?
DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate())Also 2) i want to find last friday until 3 pm.
How to do that?? Thanks a lot
-
Your expression returns a datetime, as can be seen below:
"Also 2) i want to find last friday until 3 pm." This makes no sense to me, can you clarify, please? Ideally with two or three examples.
This date is also not Friday!
-
Thanks Phil.
I have to fetch data only until friday 5 pm. So this expression gives me last friday( i need to remove -1 from my expression) with current timestamp but i want until 5 pm only. I hope u understand now. How to achieve that?? Thanks.
-
So you want an expression which returns a datetime of 5pm last Friday, is that it?
-
Yeah. Thats correct Phil.
-
-- SV: For understanding getdate() and things you need
select SYSDATETIME() as sysdatetime_FYI
, getdate() as getdate_FYI
, cast(getdate() as time) as getdate_TimeOnly
, CONVERT(varchar(15), cast(getdate() as time),100) as getdate_TimeOnly_AMPM
, DATEPART(HOUR, GETDATE())
, DATEPART(WEEKDAY, GETDATE())
-- SV: Your answer
SELECT
getdate() as getdate_FYI
,
CASE
WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
AND DATEPART(HOUR, GETDATE()) > 17
THEN
DATEADD( HOUR, 17,
DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
)
ELSE
DATEADD( HOUR, 17,
DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
)
END YourAnswer
, DATEADD( HOUR, 17,
DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
) ThisWeekFriday_5PM
, DATEADD( HOUR, 17,
DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
) LastWeekFriday_5PM -
Thanks Sreedhar but this is T-SQL Way of doing it and I want it as SSIS expression way.
-
sorry, Let me provide the SSIS ... I took your code and below article and come up with this, for Last Friday exact 5 pm:
DATEADD("HH",17,(DT_DBTIMESTAMP)((DT_STR,30,1252)(DT_DBDATE)(DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()))))clue: DATEPART("dw",GETDATE()) and went till to get the date and then added the hours.
Please check whether this helps ...
https://stackoverflow.com/questions/21704652/ssis-expression-for-date-of-previous-friday
- v_sreedhar wrote:
-- SV: For understanding getdate() and things you need
select SYSDATETIME() as sysdatetime_FYI
, getdate() as getdate_FYI
, cast(getdate() as time) as getdate_TimeOnly
, CONVERT(varchar(15), cast(getdate() as time),100) as getdate_TimeOnly_AMPM
, DATEPART(HOUR, GETDATE())
, DATEPART(WEEKDAY, GETDATE())
-- SV: Your answer
SELECT
getdate() as getdate_FYI
,
CASE
WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
AND DATEPART(HOUR, GETDATE()) > 17
THEN
DATEADD( HOUR, 17,
DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
)
ELSE
DATEADD( HOUR, 17,
DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
)
END YourAnswer
, DATEADD( HOUR, 17,
DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
) ThisWeekFriday_5PM
, DATEADD( HOUR, 17,
DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))
) LastWeekFriday_5PMConsider the following along with the fact that there is no reliance of the possibly changed "Weekday" value returned by SQL Server:
--===== Find the previous (or current if "today") Friday at 5PM.
SELECT DATEADD(dd,DATEDIFF(dd,4,GETDATE())/7*7,4)+'5:00PM';--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Sreedhar... Ur expression works but the variable has datetime datatype so it converts it to 6/7/2019 5:00:00 PM format rather than 2019-06-07 17:00:00.000. Anyway Thanks for all ur help so far. Thanks
- dallas13 wrote:
Thanks Sreedhar... Ur expression works but the variable has datetime datatype so it converts it to 6/7/2019 5:00:00 PM format rather than 2019-06-07 17:00:00.000. Anyway Thanks for all ur help so far. Thanks
I don't know SSIS to any great extent. In fact, my job in the past has been to replace SSIS packages with stored procedures and other T-SQL.
That, notwithstanding, take a look at the formula that I posted to do this in T-SQL. I'm sure that you can easily modify it for use in SSIS. If it doesn't allow direct addition of time, as I did in the formula, just add it like you would in SSIS. The key is that it accurately finds the previous Friday with the understanding that if "today" is a Friday, it will return "today".
As for formatting the output, that should be done after the formula has resolved. In T-SQL, the CONVERT function does this rather well. Surely SSIS also has such a formatting function.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Jeff. The date processing capabilities in T-SQL are significantly more powerful than those built in to SSIS (though, of course, SSIS has native access to .NET libraries through C# and VB.NET, making most things possible with a little bit of code).
In this case, v_sreedhar's expression is about as good as it gets, and that's really not very good at all, given the relative simplicity of the request (and its solution in T-SQL).
As a result of the expression's complexity, for the sake of future maintenance, I would probably either
- Use T-SQL to do it, if other SQL Server operations are being performed by the package, or
- Do it in C#
-
Yowch. Thanks for the info, Phil.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is a case where I would use C# in a script task to set variables - instead of trying to build it as an SSIS expression. The C# code can be done in a single line:
Dts.Variables["User::LastFriday"].Value = DateTime.Today.AddDays(-(int)(DateTime.Today.AddDays(-5).DayOfWeek)).Date.AddHours(17);
It also depends on what the package is doing...and whether or not I have to connect to SQL Server for other configuration values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
It also depends on what the package is doing...and whether or not I have to connect to SQL Server for other configuration values.
That same thought is usually the justification I use to convert SSIS packages to stored procedures. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply