# Calculate last friday date with timestamp ssis

• dallas13

SSCertifiable

Points: 7491

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

• Phil Parkin

SSC Guru

Points: 243939

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!

• This reply was modified 7 months, 2 weeks ago by  Phil Parkin. Reason: Additional text

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• dallas13

SSCertifiable

Points: 7491

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

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• dallas13

SSCertifiable

Points: 7491

Yeah. Thats correct Phil.

• v_sreedhar

Grasshopper

Points: 14

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

SELECT
getdate() as getdate_FYI
,
CASE
WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
AND DATEPART(HOUR, GETDATE()) > 17
THEN
)
ELSE
)
) ThisWeekFriday_5PM
) LastWeekFriday_5PM
• dallas13

SSCertifiable

Points: 7491

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

• v_sreedhar

Grasshopper

Points: 14

sorry, Let me provide the SSIS ... I took your code and below article and come up with this, for Last Friday exact 5 pm:

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 7 months, 2 weeks ago by  v_sreedhar.
• This reply was modified 7 months, 2 weeks ago by  v_sreedhar.
• Jeff Moden

SSC Guru

Points: 995503

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

SELECT
getdate() as getdate_FYI
,
CASE
WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
AND DATEPART(HOUR, GETDATE()) > 17
THEN
)
ELSE
)
) ThisWeekFriday_5PM
) 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.

--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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• dallas13

SSCertifiable

Points: 7491

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 7 months, 2 weeks ago by  dallas13.
• Jeff Moden

SSC Guru

Points: 995503

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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Phil Parkin

SSC Guru

Points: 243939

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

1. Use T-SQL to do it, if other SQL Server operations are being performed by the package, or
2. Do it in C#

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• Jeff Moden

SSC Guru

Points: 995503

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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeffrey Williams 3188

SSC Guru

Points: 88219

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:

It also depends on what the package is doing...and whether or not I have to connect to SQL Server for other configuration values.

[font="Verdana"]Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster[/url]
Managing Transaction Logs[/url]
[/font]

• Jeff Moden

SSC Guru

Points: 995503

Jeffrey Williams 3188 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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."