Insert Date and Time into Access

In the previous snippet we inserted a date into an Access database. Our new Function AccessDateTime will add the time as well in the format HH:MM:DD.

The function AccessDateTime returns the date and time in YYYY-MM-DD HH: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 AccessDateTime (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)

AccessDateTime = myYear & "-" & myMonth & "-" & myDay & " " & Time()
End Function

Dim connection
Dim SQL, sConnString

SQL="INSERT INTO tblDate (dDate) VALUES (#" & AccessDateTime(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
%>

Select date - Access database

Get the best ASP hosting with DiscountASP.NET - great value, money back guarantee.

Advertisements



MembersPro

MembersPro PayPal - ASP Membership software

Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.

Global ASP.NET Hosting Leader - Click Here