Timezone Arithmetic With Classic ASP

As a casual reader of my blog, you may know I like to do an occasionally weird post that I stick in my “General Discussion” category just for kicks. I figure that programming doesn’t always have to be a chore. It can be fun if you want it to, playing around with certain features. In this entry I play around with time zones and how you can find out a time based on a GMT offset using classic asp (that is, ASP 3.0). I also show you how you can get the UTC time from SQL server using a query and using that to find a specific time. All this and dancing girls, circus clowns, elephants that spit at the crowd and more… all on the Programming Underground!

So one day I was working on a project in classic asp (yeah I know the language is like dead and I should move on already) and needed to fetch timezones for the various client sites I service around the world. I went to look for documentation on handling GMT time and found very little useful information about how to do this in ASP. Normally I have a hard time finding documentation period because it is classic asp, but this one was a bit harder to find. PHP has done a wonderful job handling time and using universal or GMT times. Get with the program classic ASP! 😉

Not to be discouraged I forged on to find a creative solution to the problem. I knew of a little function in SQL Server (the database I am currently using for this task) to get the UTC (aka GMT aka Greenwich Meant Time) time based on the OS time that the SQL Server is installed on. It is called getutcdate() and can be queried right from an asp page. Fantastic! If I can get the UTC time, I am already half way there.

Each of the client sites I handle has a place where they can setup their own time zone offset. So for pacific time (west coast USA and Canada) the standard offset is -8 from GMT. That is, we are 8 hours behind GMT. Using the getutcdate() function and a using DateAdd function call in asp (to add or subtract time) I was able to get the time I needed for each client site.

The reason I needed this time was that if someone filled out a form on the client site, an email was generated that needed a time stamp of the site the form was hosted from, not the system time of the web server. If the form was hosted on a site from India, and was filled out, the email it generated was being sent to someone in India with a timestamp reflecting their own current time, not the time from the SQL server machine hosted here in Vancouver Canada.

So here is the little code I put together that allowed me to do this…

' Get UTC (GMT) time according to the time on the SQL Server machine
Function DateUTCTime()
	sql = "SELECT getutcdate() as utctime"
	
	Dim rsUTC
	Set rsUTC = Server.CreateObject("ADODB.Recordset")
	
	Set rsUTC = cn.execute(sql)
	
	if not rsUTC.eof then
		DateUTCTime = rsUTC("utctime")
	else
		DateUTCTime = ""
	end if
	
	Set rsUTC = Nothing
End Function


' Applies the time zone setting from the site to the fetched GMT time.
Function DateTimeZone(offset)
	
	if offset = "" or IsNull(offset) then
		timeZone = 0
	end if
	
	' Our current zone (Pacific) adjusted to GMT
	Dim dNow
	
	if DateUTCTime() <> "" then
		dNow = DateUTCTime()
	else
		' Here I am adjusting based on pacific time, so adding 8 hours would give me GMT.
		' This is a backup to not getting a valid DateUTCTime() response.
		' This would have to be changed to reflect daylight savings if needed.

		dNow = DateAdd("H",+8,now())
	end if

	' Apply time zone setting as specified in the site
	DateTimeZone = DateAdd("H",timeZone,dNow) & " GMT (" & timeZone & ":00)"
End Function

The main function to this is DateTimeZone() which takes a positive or negative offset time. This function calls the other function titled DateUTCTime() that queries SQL server using a connection I already have established for the getutcdate() function. This function is built into SQL and is not something I custom coded. This may be specific only to SQL server, so test it before implementing it on a different database platform.

After getting the UTC time from SQL I then add the timeZone offset specified in the DateTimeZone() function. Whether it is positive or negative, DateAdd will make the proper adjustment. The result is that DateTimeZone will return a string like “1-11-07 10:23:33 AM (GMT -8:00)” which is the pacific standard time using the site’s offset and the UTC value returned by SQL.

Now getutcdate() fetches GMT/UTC based on the operating system clock of where SQL server is installed. It is crucial that the server time be accurate. The functions I have written here are also designed to be internal and that the offset provided to the function be previously validated prior to calling DateTimeZone(). With this in place all the client sites are able to see form submissions in their individual time zones. Just a little code for you to tinker with on a nice Friday afternoon. Hopefully you find it useful and can be modified to fit your tastes.

Now I am off. I have a date with a 12 clowns, a tiny car, and some elephant with a feather who thinks he can fly! Yeah I am sure he can. Fly like a rock!

Thanks again for reading! 🙂