How To Code For Compatible Date Formats for Access and SQL Server
By Doug Dean
Introduction
This article was born out of my experience in writing VB DLL code for ASP that would store
dates in both Access and SQL Server when using ADO with ODBC. Rather than write code that
would require a developer to establish which of the two databases they were using, I wanted
to find a compatible date format that could be used by both. The function of this short VB
method is to return a date formatted in such a way that either Access or SQL Server will
accept it when used in a SQL statement.
Also, even though VB automatically adjusts dates to the local
international settings established with Windows, I found that international date formats
utilizing periods (.), rather than slashes (-), threw errors.
Because of this, I also include code within the method that converts all periods in the
dates to slashes.
This method is similar to the one I use in all my EZsite products, which are used in many countries that use different date formats on both Access and SQL Server. If you are unfamiliar with writing a server-side VB DLL, you can read some introductory articles at my site. You can also find trial version of my products that use this type of date management code.
The Function Definition
This method uses only two parameters and is meant to be used and called from within VB code.
The first parameter is type Date and is used to assign the date you want to
store in either Access or SQL Server. The second parameter is type Boolean and will determine
whether the returned date format is composed of just the date itself or the date along with
the time.
Public Function PrepDate(DateToConvert As Date, Optional UseTimeAndDate As Boolean = True) As String
|
The method will return a string formatted in such a way as to be acceptable to both Access and SQL Server as a date when used with ADO via ODBC.
The Code
Since this is such a simple method, and meant to be used within VB code rather than called
from an ASP page, there is no supporting database or ASP files. Just the code.
The most valuable part of this method is the two lines of code that format the date into a
string representing either the date or the date/time. Simple If ... Then code
will control for the date or date/time formats based on the value of the
UseTimeAndDate parameter.
|
Assuming that UseTimeAndDate is set to True, the following line takes the date,
that was sent as the first parameter of the method, and formats it into an Access/SQL Server
compatible date string and places it in the local method string variable strDate.
The string can then be used within an sql statement as a date argument.
strDate = "{ts '" & Format(DateToConvert, "yyyy-mm-dd hh:mm:ss") & "'}"
|
The below example illustrates how the string value assigned to strDate will be
formatted when the date #1/7/01 2:38:56 PM# is sent to the method.
{ts '2001-01-07 14:38:56'}
|
This is the format that both Access and SQL Server will accept within a SQL statement when
ADO is used in conjunction with ODBC for your database work.
Alternatively, when the optional UseTimeAndDate parameter is set to False, and
either #1/7/01 2:38:56 PM# or #1/7/01# are sent as the
DateToConvert parameter, the following string is assigned to strDate.
{d '2001-01-07'}
|
If the UseTimeAndDate parameter is set to True, or not sent and let to default
to True, and a date is sent without a time (e.g., #1/7/01#) the following would
be assigned to strDate (notice that the time is defaulted to 00:00:00,
e.g., midnight).
{ts '2001-01-07 00:00:00'}
|
We'll continue our discussion of this function in Part 2!