[RESOLVED] What is this SQL doing and - re-create in VB.Net

[FishGuy]

I need to recreate the following TSQL functionality in .Net, however I cant fugure out what it is doing within the nested parts.

I know that when run today it defaults to the 27th.

=DateAdd("d",28, DateAdd("d", -6,DateAdd("d", 1-Weekday(today),Today)))

[GaryMazzone]

Best I can figure:

DateAdd("d",28, DateAdd("d", -6,DateAdd("d", 1-Weekday(today),Today)))



| 1 | DateAdd("d", 1-Weekday(today),Today)
1. TAKE 1 - the CURRENT DAY OF the week (Sunday = 0, Monday = 1,.... ) AND ADD that number OF DAYS TO todays date.

| 2 | DateAdd("d", -6,DateAdd("d", 1-Weekday(today),Today))
2. Substract six DAYS FROM the date caclulated IN part 1

| 3 | DateAdd("d",28, DateAdd("d", -6,DateAdd("d", 1-Weekday(today),Today)))
3 ADD 28 DAYS (basicly 4 weeks) TO the date caclulated IN part 2

[FishGuy]

Based on the above I came up with the following code but unfortunately it comes out with the wrong date (11/01/2011)

 DateTime defaultstart;
            defaultstart = ((DateTime.Today.AddDays(DateTime.Today.Day)).AddDays(-6)).AddDays(28);

I am also a little confused about why take 6 off then 28, why not only take 22 off instead?

[stanav]

It should be this:

DateTime defaultstart = DateTime.Today.AddDays((1 - DateTime.Today.DayOfWeek) - 6 + 28);

However, since you have the fixed values (-6) and (+28), you might just want to combine them and do

DateTime defaultstart = DateTime.Today.AddDays((1 - DateTime.Today.DayOfWeek) + 22);

[FishGuy]

DateTime.Today.DayOfWeek of week returns "Tuesday" and cannot be implicitly cast as a double.

I also tried this
defaultstart = DateTime.Today.AddDays((1 - DateTime.Today.Day) + 22);

but this returns 23/12/2010 rather than the expected 03/01/2011


Apologies at this point, the original code is not direct TSQL it is infact a reporting services expression that I need to convert.

[stanav]

Based on your 1st and last posts, you basically needs to calculate the date of the Monday 3 weeks away from this week's Monday. For example,

'1st post:
Today = 12/10/2010
This week's Monday = 12/6/2010
Calculated date = 12/27/2010 (3 weeks from this week's Moday)

'Last post:
Today = 12/14/2010
This week's Monday = 12/13/2010
Calculated date = 1/03/2011 (3 weeks from this week's Monday)

If this is the case then you simply do:

DateTime defaultstart = DateTime.Today.AddDays((1 - (int)DateTime.Today.DayOfWeek ) + 21);

[FishGuy]

Excellent thanks