ASP Date Time Functions

Due to the problems encountered using date and times between ASP and SQL Databases I have a couple of functions for converting the Date and Time into the long format e.g. 12 January 2008. submitting the date in this format to a database allows the database to store the date in its native format so you don't have to worry if the database format is dd/mm or mm/dd.

Below you will find the code for the functions SQLDateTime and SQLDate, using these on all database interactions prevents any date formatting errors.

Usage: SQLDateTime
<%
strSQL = "INSERT INTO table (startDate, endDate) VALUES ('" & SQLDateTime(Now()) & "', '" & SQLDateTime(DateAdd("d", 1, Now())) & "')"
%>

This will add the time now and the time now + 1 day to a table called table as below in a suitable format for an SQL server database, for an Access database you would change the single quotes for #.

SQLDateTime output:
INSERT INTO table (startdate, enddate) VALUES ('08 September 2010 16:16:37', '09 September 2010 16:16:37')

Code: SQLDateTime
<%
Function SQLDateTime(currDateTime)

'Format for this function:
'
' SQLDateTime(DateTime)

Dim tempDate

tempDate = fillDigit(Day(currDateTime))

tempDate = tempDate & " "

tempDate = tempDate & MonthName(Month(currDateTime))

tempDate = tempDate & " "

tempDate = tempDate & Year(currDateTime)

SQLDateTime = tempDate & " " & FormatDateTime(currDateTime, 3)

tempDate = ""

End Function
%>

Below is the same function with the time removed as using SQLDateTime without a time i.e. using Date() It will automatically set the time to 00:00 midnight

Code: SQLDate
<%
Function SQLDate(currDate)

'Format for this function:
'
' SQLDate(Date)

Dim tempDate

tempDate = fillDigit(Day(currDate))

tempDate = tempDate & " "

tempDate = tempDate & MonthName(Month(currDate))

tempDate = tempDate & " "

tempDate = tempDate & Year(currDate)

SQLDate = tempDate

tempDate = ""

End Function
%>

You will also need the following function that is used in the SQLDateTime Function.
This function simply adds a zero to the front of a number if it is one character in length, this is of particular use when forming dates for SQL Strings

Code: FillDigit
<%
Function fillDigit(intDigit)

Select Case len(intDigit)
Case 1
fillDigit = "0" & intDigit
Case Else
fillDigit = intDigit
End Select

End Function
%>

If you add these three functions to an include file that gets included in all pages where you have database interaction the functions will always be available.

<!--#include virtual='/includes/advancedDateFunctions.inc'-->