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
%>
Insert a date and time into an Access database
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.
Get the best asp web hosting provider now and save 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
