Taming Dates in ASP

Written by Nick Dunn published 8th Mar 2006 | Comment on this article

A function to save dates into a generic format, regardless of database type or server localised settings.

Taming Dates in ASP

Introduction

When you?re using dates in calculations in ASP, or saving them to Access or SQL databases, you can often encounter very bizarre and illogical results. Don?t use messy Session.LCID hacks. Here are some reusable functions which should solve all of your date problems forever...

I have found that the best way to achieve uniform results when saving and retrieving dates to and from a database, is to store a date as an integer (number) value. The obvious way is to store in the following format:

yyyymmddhhmmss

Therefore the latest date is always the largest number (useful for ordering and selecting newest records).

How it's done

Here's the function I use to create a number in the above format. Make sure you pass the full current date AND time (Now()) and not just the current date (Date()):

Function saveDate(theDate)
If isNull(theDate) or CStr(theDate) = "" Then
saveDate = ""
Else
strMonth = Month(theDate)
strDay = Day(theDate)
strHour = Hour(theDate)
strMinute = Minute(theDate)
strSecond = Second(theDate)
If Len(strMonth) = 1 Then strMonth = "0" & strMonth End If
If Len(strDay) = 1 Then strDay = "0" & strDay End If
If Len(strHour) = 1 Then strHour = "0" & strHour End If
If Len(strMinute) = 1 Then strMinute = "0" & strMinute End If
If Len(strSecond) = 1 Then strSecond = "0" & strSecond End If
If IsDate(theDate) Then
saveDate = Year(theDate) & strMonth & strDay & strHour & strMinute & strSecond
End If
End If
End Function

When it comes to display the date on a page I pass the retrieved database integer and a pre-defined format type to a "formatDate" function:

Function formatDate(theDate, displayType)
If isDate(theDate) Then
returnDate = True
strYear = Year(theDate)
strMonth = Month(theDate)
strDay = Day(theDate)
strHour = Hour(theDate)
strMinute = Minute(theDate)
strSecond = Second(theDate)
If Len(strMonth) = 1 Then strMonth = "0" & strMonth End If
If Len(strDay) = 1 Then strDay = "0" & strDay End If
If Len(strHour) = 1 Then strHour = "0" & strHour End If
If Len(strMinute) = 1 Then strMinute = "0" & strMinute End If
If Len(strSecond) = 1 Then strSecond = "0" & strSecond End If
ElseIf len(theDate) = 14 Then
returnDate = True
strYear = Left(thedate,4)
strMonth = Mid(theDate,5,2)
strDay = Mid(theDate,7,2)
strHour = Mid(theDate,9,2)
strMinute = Mid(theDate,11,2)
strSecond = Mid(theDate,13,2)
Else
returnDate = False
End If
If returnDate Then
Select Case displayType
Case 0
If Left(strDay,1) = "0" Then strDay = Right(strDay,1) End If
formatDate = WeekDayName(WeekDay(strDay)) & " " & strDay & "<sup>" & formatDateAbbrev(strDay) & "</sup> " & MonthName(strMonth) & ", " & strYear
Case 1
formatDate = strDay & "/" & strMonth & "/" & strYear
Case 2
formatDate = strDay & "/" & strMonth & "/" & strYear & " " & strHour & ":" & strMinute
Case 3
formatDate = strHour & ":" & strMinute
End select
End If
End Function

This also makes use of a "formatDateAbbrev" function which returns the "st", "nd", "rd" and "th" day abbreviations:

Function formatDateAbbrev(theDay)
If isNull(theDay) Or cstr(theDay) = "" Or Not isNumeric(theDay) Then
formatDateAbbrev = ""
Else
If theDay = 1 Or theDay = 21 Or theDay = 31 Then
formatDateAbbrev = "st"
ElseIf theDay = 2 Or theDay = 22 Then
formatDateAbbrev = "nd"
ElseIf theDay = 3 Or theDay = 23 Then
formatDateAbbrev = "rd"
Else
formatDateAbbrev ="th"
End If
End If
End Function

There is also an optional function which allows me to pull out a specific part of a date when it's needed:

Function getDatePart(theDate,thePart)
Select Case thePart
Case "year"
getDatePart = left(thedate,4)
Case "month"
getDatePart = mid(theDate,5,2)
Case "day"
getDatePart = mid(theDate,7,2)
Case "hour"
getDatePart = mid(theDate,9,2)
Case "minute"
getDatePart = mid(theDate,11,2)
Case "second"
getDatePart = mid(theDate,13,2)
End Select
End Function

On the forums Paul Creedy points out an alternative method of storing dates in a normal Date field in the format "yyyy/mm/dd" which should also achieve the same results, but I've not tried it. One slight caveat may be compatibility with different database types. When querying an Access database we must surround our dates using a hash (#), e.g.

Select * From tblData Where fldDate = #11/01/2005#

But it is entirely possible that other databases (SQL server, MySQL etc) use different characters for selecting dates in SQL. I'm fairly certain they use uniform syntax for selecing numerical values, so the method described above may be best applied in situations where you may need to change between two or more database types. The best way, of course, is to experiment for yourself ;-)