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 ('08 September 2010 16:16:37', '09 September 2010 16:16:37')<% 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'-->