There are a few different situations where your program can use Dates with databases, and below are explanations of how they should be dealt with.


Before we start, I'll remind you of a very important point that some people have a problem understanding - there is a very big difference between a String and a Date.

If you aren't sure of the difference between a String and a Date, or are having problems with dates in the non-database parts of your VB code, see the article Why are my dates not working properly? from our Classic VB FAQs.

What matters here is the data type of the field in the database, if it is a String/Char/Text then deal with it as a String, but if it is a Date/DateTime/Time then deal with it as a Date (if you aren't sure what data type a field is, look at the table design screen in your database software).

In this article we are only dealing with Date fields, but if your field is a String/Char/Text field (which contains a date value in a particular format), you can convert it to a Date once it reaches your program. If you want to see how to do that, go on over to the article I linked to above - as it contains example code to convert a formatted String to a Date.


Reading/writing Date fields in a RecordSet
The fields of a RecordSet object mimic the data type of the field in the database, so if the database field is a Date you can simply put the value of that field into a Date variable, or put the value of a Date variable/function into that field. eg:
Code:
'These examples assume rs is your recordset
Dim MyDateVariable as Date
  MyDateVariable = rs.Fields("MyDateField").Value  'Read the field into our variable

  rs.Fields("MyDateField").Value = Date            'Set the field value to todays date 
  rs.Fields("MyDateField").Value = MyDateVariable  'Set it to the same as our variable
  rs.Fields("MyDateField").Value = DateAdd("d", 1, Date)  'Set it to tomorrows date
(I have used the full syntax of rs.Fields("MyDateField").Value here, but the same method applies to other variations like rs("MyDateField").Value and rs!MyDateField)

Note that you should never try to use the Format function or the Date$ function (with a $) to put a date value into a recordset field, as those functions return Strings rather than Dates - and those strings will need to be converted back into Dates again. As explained in the link near the top of this article, that process is prone to error (as well as being an unnecessary step!).
Code:
Dim MyDateVariable as Date
  MyDateVariable = Date    
    ** these all add unsafe String to Date conversions ** '(the proper versions are above)
  rs.Fields("MyDateField").Value = Format(Date,"m/d/yyyy")
  rs.Fields("MyDateField").Value = Format(MyDateVariable ,"m/d/yyyy")
  rs.Fields("MyDateField").Value = Date$
To see how to deal with date values in the Select statement that opens the recordset, see the SQL Statements section below.


Putting Date values into Parameters for the Command object
In the same way as using any other data type with a Command object, there are two stages:
  1. If the value is to be listed in the statement (like with an Insert statement), you simply put a ? into the CommandText (without any delimiters around it) for the placement of the value.
  2. Add the parameter with the appropriate Type (typically adDate, but check the help to be sure), and the value.


Here is an example of an Insert statement (which specifies a VarChar field and a Date field):
Code:
Dim MyDateVariable as Date
Dim adoCommand As ADODB.Command
  MyDateVariable = Date
  Set adoCommand = New ADODB.Command
  With adoCommand
    .ActiveConnection = adoConnection
    .CommandType = adCmdText 

        'Step 1 - add the ? to mark the position
    .CommandText = "INSERT INTO test (MyVarCharField, MyDateField) VALUES(?,?)"
    .Prepared = True
    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, "some text")

        'Step 2 - add the appropriate parameter
    .Parameters.Append .CreateParameter(, adDate, adParamInput, , MyDateVariable)
    .Execute , , adCmdText + adExecuteNoRecords
  End With
  Set adoCommand = Nothing
..and an example of calling a Stored Procedure called MySP (with a single Date parameter):
Code:
Dim MyDateVariable as Date
Dim adoCommand As ADODB.Command
  MyDateVariable = Date
  Set adoCommand = New ADODB.Command
  With adoCommand
    .ActiveConnection = adoConnection
    .CommandType = adCmdStoredProc

        'Step 1 - no need to mark the position for a Stored Proc parameter
    .CommandText = "MySP"
    .Prepared = True

        'Step 2 - add the appropriate parameter
    .Parameters.Append .CreateParameter(, adDate, adParamInput, , MyDateVariable)
    .Execute
  End With
  Set adoCommand = Nothing
Note that as with a RecordSet, it is not appropriate to use Format or Date$ (with a $), as they will need to be converted from String to Date:
Code:
    ** these add unsafe String to Date conversions **
    .Parameters.Append .CreateParameter(, adDate, adParamInput, , Format(MyDateVariable, "mm/dd/yyyy"))
    .Parameters.Append .CreateParameter(, adDate, adParamInput, , Date$)
..instead, simply use the Date variable by itself (or another Date value, such as the Date function) as shown earlier.

To see how to deal with date values in the CommandText that aren't specified by parameters, see the SQL Statements section below (but you will probably find it easier/safer to add a parameter for them instead!).


Using Date values in SQL Statements (Select / Insert / Update / Delete / ...)
No matter what kind of SQL statement you are using (Select / Insert / calling a stored procedure / etc), you need to format date values appropriately for the database system that you are working with - and unfortunately most database systems have their own syntax to do that.

An important point to remember is that an SQL statement (no matter how or where you run it) is a String, and so you need to convert any Date value you have into a String in a particular format.

In general, your value should be enclosed in some sort of delimiter (otherwise it is a mathematical calculation!), and very importantly it should be formatted in either the US format (mm/dd/yyyy) or arguably safer the ISO format (yyyy-mm-dd), no matter what the actual settings are on your computer or in the database itself - as the SQL language (like many other things in the computer world) is based on US standards.


For explanations and examples of the syntax for your database system, see the Dates section of the article How do I use values (numbers, strings, dates) in SQL statements?

If you find that none of the syntaxes listed in that article work for you, please let me know so that I can update the list appropriately.