kus2nd's mumbling

how to get last day of month in VBScript

To get today's date, we can use now or date function in VBScript. Actually now function returns current date and time of your computer (i.e. Wednesday, September 15, 2004 13:09:23), while date returns only the date in mm/dd/yyyy (i.e. 9/15/2004). That's how we get current date.
So how to get the last day of this month? First, let's see DateSerial(Year, Month, Day) function. DateSerial(2004,9,15) returns 9/15/2004. We could use this function to get the last day of this month. The following codes show how to do this.

theday = date
lastdayofthemonth = DateSerial(Year(theday), _
   Month(theday) + 1, 0)

My explaination for this is we want to define a date where the date is the 0th date of next month, or in other words the last date of the month before next month (this month). You can also use this code to find the last day of another month by changing theday variable.
The next topic I want to discuss from this paragraph is a little bit different from the above paragraphs. It's about how to get the last day of each month from the database. For example, I have a table that contains date and how many visitors I have until that date. I want to have data of total visitors I have until the last day of each month. Not only that, I also want to have data of total visitors until the last day of each year, and data of visitors each day. The following code is under assumption that the data already ordered by date descending (for the latest to the oldest)

dim county1, countm1, countd1
dim countylast, countmlast, countdlast 
dim tgl2
tgl2 = date
countylast = Year(tgl2)
countmlast = Month(tgl2)
countdlast = Day(tgl2)

Dim arramt, arrctr
arramt = 1 
arrctr = 0
Redim arrdt0(arramt)
Redim arrdt1(arramt)

do while not rs.EOF
   if radioch="permonth" or radioch="peryear" then
      county1 = Year(rs.Fields("xdate"))
      countm1 = Month(rs.Fields("xdate"))
      countd1 = Day(rs.Fields("xdate"))
   end if
   if ((radioch="peryear" and county1<=countylast) or _
      (radioch="permonth" and county1<=countylast and _
      countm1<=countmlast) or radioch="perday") then
      if radioch="peryear" then
         if county1<countylast then 
            countylast = county1 - 1
         else
            countylast = countylast - 1 
         end if		
      elseif radioch="permonth" then
         if countm1<countmlast then
            countmlast = countm1 - 1
         else
            countmlast = countmlast -1
         end if
         if countmlast=0 then 
            if county1<countylast then 
               countylast = county1 - 1
            else
               countylast = countylast - 1 
            end if
            countmlast=12
         end if
      end if
     
      if arrctr>=arramt then
         arramt = arramt+5
         redim preserve arrdt0(arramt)
         redim preserve arrdt1(arramt)
      end if
      arrdt0(arrctr)=rs.fields("xdate").Value
      arrdt1(arrctr)=cLng(rs.fields("xvisitor").Value)
      arrctr = arrctr+1
   end if

   rs.MoveNext
loop

I keep the data I want into an array. If variable radioch is set to "perday" then data total visitor each day will be kept. If variable radioch is set to "permonth" then data total visitor on last day for each month will be kept. If variable radioch is set to "peryear" then data total visitor on last day for each year will be kept.
Critics, suggestions, and comments always welcomed.

posted by kus @ 11:35 AM