Calculate last friday date with timestamp ssis

Question

Post reply

Calculate last friday date with timestamp ssis

dallas13

SSCertifiable

Points: 7491
More actions
June 13, 2019 at 10:11 pm

#3653557
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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Your expression returns a datetime, as can be seen below:

2019-06-13_17-28-04

"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!

This reply was modified 5 years, 5 months ago by Phil Parkin. Reason: Additional text

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

So you want an expression which returns a datetime of 5pm last Friday, is that it?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

dallas13

SSCertifiable

Points: 7491

June 13, 2019 at 10:44 pm

#3653568

Yeah. Thats correct Phil.

v_sreedhar Grasshopper Points: 16 More actions · Answer 5

-- 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

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

Thanks Sreedhar but this is T-SQL Way of doing it and I want it as SSIS expression way.

v_sreedhar Grasshopper Points: 16 More actions · Answer 7

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

This reply was modified 5 years, 5 months ago by v_sreedhar.
This reply was modified 5 years, 5 months ago by v_sreedhar.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

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_5PM

Consider 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)

dallas13 SSCertifiable Points: 7491 More actions · Answer 9

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

This reply was modified 5 years, 5 months ago by dallas13.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

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#

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

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)