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.
<%
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 #.
INSERT INTO table (startdate, enddate) VALUES ('20 May 2012 15:19:10', '21 May 2012 15:19:10')<% 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
<% 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
<% 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'-->
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
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