Converting day, weeknr and year to date

Question

I've been given an excel document in which worktime information is noted, this document contains certain columns which are being read by using SSIS in visual studio, after that the information is writen to a Database.

The week and year column contain the week number and the year, the columns Monday up to Friday contain information about how many working hours have been spent on a certain task on that day of the week.

What I'd like to do is take the WeekNr, Year and Day and convert these into a date. I've been trying to accomplish this by using a script component that converts a day number, week number and year to a date but so far I haven't been able to get the day number from the columns. In my opinion it would work best if used with a start and end date taking the first and last date of that week.

So my question is if someone knows how to accomplish this, or if I should try a different approach.

The script component:

     public override void Input0_ProcessInputRow(Input0Buffer Row, CultureInfo cultureInfo, int day )
{

    DateTime firstDayOfYear = new DateTime(Int32.Parse(Row.Jaar), 1, 1);
    int firstWeek = cultureInfo.Calendar.GetWeekOfYear(firstDayOfYear, cultureInfo.DateTimeFormat.CalendarWeekRule, cultureInfo.DateTimeFormat.FirstDayOfWeek);
    int dayOffSet = day - (int)cultureInfo.DateTimeFormat.FirstDayOfWeek + 1;
    Row.TaskDates = firstDayOfYear.AddDays((Int32.Parse(Row.Week) - (firstWeek + 1)) * 7 + dayOffSet + 1);



}

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

Based on this answer, I think you want something like the following. This result gives you Monday's date, so you can just AddDays based on the column day of the week.

DateTime jan1 = new DateTime(Int32.Parse(Row.Jaar), 1, 1);
int daysOffset = DayOfWeek.Monday - jan1.DayOfWeek;

DateTime firstMonday = jan1.AddDays(daysOffset);
var cal = CultureInfo.CurrentCulture.Calendar;

int firstWeek = cal.GetWeekOfYear(firstMonday, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

var weekNum = Int32.Parse(Row.Week);
if (firstWeek <= 1)
{
    weekNum -= 1;
}
var mondaysDate = firstMonday.AddDays(weekNum * 7);
var tuesdaysDate = mondaysDate.AddDays(1);

edited May 23 '17 at 10:26

Community

1
1

answered Sep 23 '15 at 12:47

sorrell

1,801
1
16
27

Any idea on how to split a single row into multiple rows for when multiple days are filled? – Alim Sep 25 '15 at 10:12
Given the above script, you'll want to access each row's column contents by looking the value in Row.Monday, Row.Tuesday, etc... – sorrell Sep 25 '15 at 10:32
Well I did that but it only worked for single days and not when multiple days are filled. I used: if(monday != null){ daysOffset = DayOfWeek.Monday - jan1.DayOfWeek;} – Alim Sep 25 '15 at 10:59
I edited the answer - does that help give you an idea of how to get all of the weekdays' dates? – sorrell Sep 25 '15 at 11:37
Yes, I got that point but I meant something different, lets say the meetings task has 2 values one on tuesday and one on thursday. If I have rows like this I want to split it into 2, meaning one row for tuesday and one row for thursday. – Alim Sep 25 '15 at 11:42
You need to utilize `OutputBuffer0.AddRow` - [see this question](http://stackoverflow.com/questions/9573734/ssis-script-component-split-single-row-to-multiple-rows-parent-child-variati) – sorrell Sep 25 '15 at 11:48

Converting day, weeknr and year to date

1 Answers1