Working with Dates in Reports

[ToMonkey]

OK, So I'm looking at writing a reboot report I've been working on this for and age.

Essentially I want to take the date today increase the month by one and start from the 8th of that month to find out when our first day to start rebooting is (2nd Tuesday of the month), then dependent on the asset calculate a day from that value, so site A is +1, Site B of plus 2 etc etc


For the life of me I cannot cut up the date and put it back together, I can get the individual bits
TheDay = DateDiff(MONTH, 0, GetDate()),
DayofTheWeek = DatePart(dw, GetDate()),
MonthofTheYear = DatePart(mm, GetDate()),
TheYear = DatePart(yyyy, GetDate()),

But I can't put them back together. i.e. TheRebootDay1 = Day + Month + Year

Then I can'f for example use the fields I create even something simple like.

Year2 = TheYear

This gives me errors, even though TheYear will come out OK in the Report. I don't really understant whats going on.

Can anyone help I know its something stupid but never really done thsi before.

[RCorbeil]

My admin was able to point me to an SQL 2012 server on our network, so I tried using DATEFROMPARTS() instead of

CONVERT(DateTime, CONVERT(VarChar(50), (@Year*10000 + @Month*100 + @Day)), 112)

DateAdd(dd,
        -1*DatePart(dw, DATEFROMPARTS(DatePart(yyyy, DateAdd(mm, 1, GetDate())), DatePart(mm, DateAdd(mm, 1, GetDate())), 8-3) ),
        DATEFROMPARTS(DatePart(yyyy, DateAdd(mm, 1, GetDate())), DatePart(mm, DateAdd(mm, 1, GetDate())), 1+7*2)
       )

[RCorbeil]

We're working with simple queries, not stored procedures, so you don't have the "just assign it to a variable and calculate from there" option.

From some quick Googling, it's not horribly difficult to calculate the second Tuesday of a given month. Most of the examples I found were the same and were examples of how to do it in Excel, but we can work with that.

Example (source😞

=DATE(@year, @month, 1+7*n) - WEEKDAY(DATE(@year, @month, 8-xday))

where 'xday' is the day of the week we're after (1=Sun, 2=Mon, 3=Tue, ...) and 'n' is the nth instance of that day in the month.

For the second Tueday, then, n=2 and xday=3

=DATE(@year, @month, 1+7*2)-WEEKDAY(DATE(@year, @month, 8-3))

We don't have the =Date(yyyy, mm, dd) function to work with, so I'll refer back to

CONVERT(DateTime, CONVERT(VarChar(50), (@Year*10000 + @Month*100 + @Day)), 112)

Likewise, we can't just subtract a number from a date like Excel, so we'll need to use

DateDiff(dd, -1*days, basedate)

If I haven't mangled things, this should be the second Tuesday of next month:

DateAdd(dd,
        -1*DatePart(dw, CONVERT(DateTime, CONVERT(VarChar(50), (DatePart(yyyy, DateAdd(mm, 1, GetDate()))*10000 + DatePart(mm, DateAdd(mm, 1, GetDate()))*100 + 8-3)), 112) ),
        CONVERT(DateTime, CONVERT(VarChar(50), (DatePart(yyyy, DateAdd(mm, 1, GetDate()))*10000 + DatePart(mm, DateAdd(mm, 1, GetDate()))*100 + 1+7*2)), 112)
       )

I Googled up a few examples that did essentially the same thing but generalized it by creating a function. Assuming you want to keep your "report" (query) self-contained, this should do the job.

[ToMonkey]

OK so I have it now where I can get the second Tuesday of the month as below.

Why can I not reference RebootDate in a formula for another field, how would I do this?

Case
When DateName(dw, DateAdd(month, 1, DateAdd(day, 8 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
8 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 9 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
9 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 10 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
10 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 11 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
11 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 12 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
12 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 13 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
13 - DatePart(dd, GetDate()), GetDate()))
When DateName(dw, DateAdd(month, 1, DateAdd(day, 14 - DatePart(dd,
GetDate()), GetDate()))) = 'Tuesday' Then DateAdd(month, 1, DateAdd(day,
14 - DatePart(dd, GetDate()), GetDate()))
End As RebootDate,

[ToMonkey]

Thanks for that I already have part of it done as follows:

NextRebootBase = DateAdd(month, 1, DateAdd(day, 8 - DatePart(dd, GetDate()), GetDate()))

Still don't understand though why I can't use the NextRebootBase field as the source for other calcs i.e.

NextBase1 = NextRebootBase

Why won't this work?

[RCorbeil]

If you can stitch the date elements into a recognizable format, you should be able to cast the result into a Date or DateTime value. See here.

Someone asked about doing this over at StackOverflow and examples were provided.

Basically, if you're using SQL Server 2012+,

SELECT DATEFROMPARTS (@Year, @Month, @Day);

otherwise,

SELECT CONVERT(Date, CONVERT(VarChar(50), (@Year*10000 + @Month*100 + @Day)), 112);

If your SQL Server version doesn't support converting to a Date type, make it a DateTime instead.

SELECT CONVERT(DateTime, CONVERT(VarChar(50), (@Year*10000 + @Month*100 + @Day)), 112);