Codefixer - Insert Date into Access Database

When inserting a date into an Access database you should use the format YYYY-MM-DD. The function AccessDate returns a date in YYYY-MM-DD format.
When working with a date in Access always use the # delimiters in your SQL statement.

The database is called 'dbDate', the table 'tblDate' and date field 'dDate'. The 'dDate' field is a DATE/TIME datatype.

<%
Function AccessDate(dateandtime)
Dim myDay
Dim myMonth
Dim myYear

myDay = Day(dateandtime)
If Len(myDay)=1 Then myDay="0" & myDay

myMonth = Month(dateandtime)
If Len(myMonth)=1 Then myMonth="0" & myMonth

myYear = Year(dateandtime)

AccessDate = myYear & "-" & myMonth & "-" & myDay
End Function

Dim connection
Dim SQL, sConnString

SQL="INSERT INTO tblDate (dDate) VALUES (#" & AccessDate(NOW()) & "#)"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("datedb.mdb")

Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
connection.execute(SQL)
Connection.Close
Set Connection = Nothing
%>

MM/DD/YYYY means different things to different people. 12/03/1974 to Americans reads as the 3rd of the 12th 1974, to British and Irish it reads the 12th of the 3rd 1974. The only safe format to use is YYYY-MM-DD in Access where there can be no misunderstandings.

If you have any code snippets to share with full credit given then send an email to Codesnippets - You'll receive full credit and a link back to your site.