When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Wednesday, January 17, 2001

How To Code For Compatible Date Formats for Access and SQL Server

By Doug Dean

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.

- continued -

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.

If UseTimeAndDate Then
  '---- DATE & TIME
  strDate = "{ts '" & Format(DateToConvert, "yyyy-mm-dd hh:mm:ss") & "'}"
  '----- DATE
  strDate = "{d '" & Format(DateToConvert, "yyyy-mm-dd") & "'}"
End If

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!

  • Read Part 2!

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article