how to find number of sundays and saturdays between two given dates in ssis ?

Question

How to find number of sundays and saturdays between two given dates..?

i have an excell file, having two date columns and i want to know number of working days between that two days.

what ll be the expresson in SSIS  derived column.

Please suggest me...

---

Thanks & Regards $@m

Answer 1

You can use the expression DATEPART( "dw", getdate() ) to get the day of week, but since SSIS Expressions are not dynamic you need to either feed it in a loop and not use the Derived Column Transformation, or I suggest you simply develop a Script Task that you can feed with the date range and populate another variable with the result.

An advantage of writing in .Net code is that you can even unit test it outside SSIS e.g. in a Console Application.

---

Arthur My Blog

Answer 2

Hello its..

With This Query you can Select Saturdays and Sundays  from date1 and date2..

declare @Start_date DateTime, @end_Date DateTime
select @Start_date  = '20120801', @end_Date  = '20120831'

;WITH Date_Result(DateofWeek) AS (
SELECT @Start_date  fecha
UNION ALL
SELECT DATEADD(day, 1, dateofweek) Dateofweek
FROM Date_Result 
WHERE DateofWeek < @end_date
)
select Dateofweek from Date_result where DATEPART(dw,Dateofweek)in (1,7)
OPTION (MaxRecursion 0);

For any Question About, Don't hesitate and contact me again..

Best Regards.

---

I hope Help you Leonardo Martínez

Answer 3

Refer this thread which has SQL queries for getting the working days. Flip the logic to get the number of saturdays and sundays.

---

http://btsbee.wordpress.com/

Answer 4

Could you please provied me the .Net Script am not aware of Coding. i guess it would be small code.

---

Thanks & Regards $@m

Answer 5

You do not need a .NET code to achieve this. You can create an execute SQL Task and use Leonardo Martínez P SQL code to get the count. find below the snapshot of how your ExeCute SQL task should look like:

The Query is set to get total number of First (Sunday) and 7th (Saturday) days of the week between 2 dates. 

Thanks Leonardo Martínez, for sharing such a concise piece of code.

---

Please mark the post as answered if it answers your question

Answer 6

Hello Arthurz, Could you please provied me .Net code to get the number of working days in between two dates of ExCell Sheet.

exp columns:

col1                                          col2                                             col3

Startdate(string)                   EndDate (date)                           number Working days(sould be in int)

i guess this can be done only through .Net code only... since this data is in Excell sheet.

---

Thanks & Regards $@m

Answer 7

Hello Leonardo , Could you please provied me .Net code to get the number of working days in between two dates of ExCell Sheet.

exp columns:

col1                                          col2                                             col3

Startdate(date)                   EndDate (date)                           number Working days(sould be in int)

i guess this can be done only through .Net code only... since this data is in Excell sheet.

---

Thanks & Regards $@m

Answer 8

Hi Sam,

You can use the below mentioned SQL Code and try calling this function

create function workhours_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin

return CAST(
                (
                  (DATEDIFF(dd,@StartDate,@EndDate)+1)
                 -(DATEDIFF(wk,@StartDate,@EndDate)*2) 
                 -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
                 -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
                 )
                -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
                       ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
                -(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0 
                       ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
        AS FLOAT) * 24
end
go

---

Ramasubramanian S

Answer 9

Hello Leonardo , Could you please provied me .Net code to get the number of working days in between two dates of ExCell Sheet.

exp columns:

col1                                          col2                                             col3

Startdate(date)                   EndDate (date)                           number Working days(sould be in int)

i guess this can be done only through .Net code only... since this data is in Excell sheet.

---

Thanks & Regards $@m

Hi its me

Easy Way. :

1.- Load your Data  by SSIS  From Excel To SQL Table

2.- Apply SQL CODE

3.- and Ready..

---

I hope Help you Leonardo Martínez

Answer 10

Sam,

Did any of the above make the trick?

---

Arthur My Blog

Answer 11

ll this SQL Code works on Excel sheet. here your passing values through parameters and giving them manually. but i need my package should take the values form the excel sheet STARTDATE column and ENDDEATE Coulmn.

---

Thanks & Regards $@m