Next Month

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",2,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Can’t believe I wrote that function.

=dateadd(“d”, –1, dateadd(“m”, +2, cdate(year(today()) & “/” & month(today()) & “/1”)))

that’s the expression that gives you the last day of the next month in Reporting Services

My favorite functions

In a previous message, I wrote about some functions on time. Here are my favorite functions... much better than the previous ones

function GetLastDayPreviousMonth()
Dim dt
Dim firstDay
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastday = DateAdd("d",-1,firstDay)
GetLastDayPreviousMonth = lastDay
end function
function GetFirstDayPreviousMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayPreviousMonth()),month(GetLastDayPreviousMonth()),1)
GetFirstDayPreviousMonth = firstDay
end function

Sillyness.. absolutely :

=dateadd(“d”, –1, dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”)))

will give you the last day of the previous month.

the first day is easier still :

=dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”))

 

 

 

function convertTime(intMinutes)
dim iTimeInHours, iTimeMinutes,strTimeMinutes, strTimeInHours, strTimeInHoursAndMinutes
iTimeInHours = intMinutes \ 60
iTimeMinutes = intMinutes mod 60
if (iTimeInHours < 10) then
strTimeInHours = "0" & cstr(iTimeInHours)
else
strTimeInHours = cstr(iTimeInHours)
end if
if (iTimeMinutes < 10) then
strTimeMinutes = "0" & cstr(iTimeMinutes)
else
strTimeMinutes = cstr(iTimeMinutes)
end if
strTimeInHoursAndMinutes = strTimeInHours & ":" & strTimeMinutes convertTime = strTimeInHoursAndMinutes
end function
function calculateTime(eDt as datetime, sDt as datetime, eLu as Int32)
dim iTime
iTime = DateDiff("n",eDt,sDt) - eLu calculateTime = iTime
end function

Dates in Reporting Services

A colleague of mine asked for a parameter on a report. The report had to run from the first of the previous month, until the last day of the previous month. I solved this with this function :

function fnGetLastDayOfMonth(datum as date)
fnGetLastDayOfMonth = Day(DateSerial(Year(datum), Month(datum) + 1, 0))
end function

I then called this function in this way :

startdate

=cdate(iif(month(now())=1,year(now())-1,year(now())) & "/" & iif(month(now())=1,12,month(now())-1) & "/01")

enddate

=cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,12) & "/" & code.GetLastDayOfMonth(cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,1) & "/" & "1")))

can’t believe I wrote that function – this will do the trick just fine.

=dateadd(“d”, –1, dateadd(“m”, +1, cdate(year(today()) & “/” & month(today()) & “/1”)))

from the parameter. It would then feed the first day of the previous month to that function and there you have it.. the last day. Cool.

Date input selectors in Reporting Services

When you create a parameter in Reporting Services which has Date/Time as a format, it uses the a date format which is based on the settings of internet explorer. Go to tools / internet options to set the language. If you set the language to English US, then the format will be : mm/dd/yyyy, if you set it to dutch/belgium it will default to dd/mm/yyyy