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 ('20 May 2012 15:19:10', '21 May 2012 15:19:10')

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.

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

ASP Date Time Comments

Please feel free to leave comments, questions or suggestions here, I will respond to questions as soon as possible but please leave your correct email address as I sometimes email responses to questions.

Add Comment

Posted By: James Date: 15/11/2011 11:30

 Hi

Thanks for this code it has saved me hours of debugging, to be honest i thought the date had to be passed as a dd/mm/yyyy, mm/dd/yyyy or whatever format your db is set to but passing it as text and letting the database sort it out is pure genius.

The times i have a site running and almost forgotten about, then the date error throws up it's ugly head and i have to spend hours trying to find the problem. Now i just use your code as a standard include on all my projects and the problem never happens.

Thanks again.

Regards James

Total Comments: 1