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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, January 17, 2001

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

By Doug Dean


Read Part 1


In Part 1 we looked at the basics for our function that converts a standard date/time value into an Access and SQL-ready date/time format. In this part we'll look at protecting ourselves against periods in certain date formats and (finally) present the complete code of the function!

- continued -

Extra Code for Period Problems
That would be the full extent of the code except for some date formats that utilize periods in place of slashes. To avoid this problem, five lines of code will check for periods within the date string and substitute slashes if found.

   '////// ESCAPE ANY PERIODS FOR SOME INTERNATIONAL DATES
   intIndex = InStr(strDate, ".")
   Do While intIndex
      strDate = Left(strDate, intIndex - 1) & "-" & _
                Right(strDate, Len(strDate) - intIndex)
      intIndex = InStr(strDate, ".")
   Loop

Now all that is left to do is assign the strDate variable, which holds our formatted date string, to the method name so it can be sent back to the calling method.

   PrepDate = strDate
End Function

Calling the PrepDate() Method
Below you'll find an example of calling this method from a VB DLL. The following line uses the PrepDate() method to create a date-safe SQL statement. It does this by inserting the formatted date string into a type Date database field (YourDateFieldName) of a valid sql statement. This INSERT sql statement can be used with ADO via ODBC for either Access or SQL Server. The #1/7/01 2:38:56 PM# date is used, but any valid date can be used, like PrepDate(Now).

MyDateVar = #1/7/01 2:38:56 PM#
strSql = "INSERT INTO YourTableName (YourDateFieldName) " & _
         "VALUES ('" &  PrepDate(MyDateVar) & "') WHERE MyID = 1"

This line of code will result in a valid SQL statement assigned to the strSql string variable. For example, when I ran the above code, strSql contained the following value:

INSERT INTO YourTableName (YourDateFieldName) VALUES ('{ts '2001-01-07 14:38:56'}') WHERE MyID = 1

Here is an example of a SELECT sql statement using PrepDate():

MyDateVar = #1/7/01 2:38:56 PM#
strSql = "SELECT YourDbField FROM YourTableName " & _
         "WHERE YourDateFieldName = " & PrepDate(MyDateVar)

Which results in the strSql string value below.

SELECT YourDbField FROM YourTableName WHERE YourDateFieldName = {ts '2001-01-07 14:38:56'}

Here's the entire prepDate() code that you can cut and paste it into your VB code!

Public Function PrepDate(DateToConvert As Date, _
                         Optional UseTimeAndDate As Boolean = True) _
                            As String

   '//////// DECLARE TWO METHOD VARIABLES
   Dim strDate As String
   Dim intIndex As Integer

   '/////// ASSIGN A FORMAT BASED ON 'TIME STAMP' OR 'DATE'
   If UseTimeAndDate Then
       '---- DATE & TIME
       strDate = "{ts '" & Format(DateToConvert, _
                              "yyyy-mm-dd hh:mm:ss") & "'}"
   Else
       '----- DATE
       strDate = "{d '" & Format(DateToConvert, "yyyy-mm-dd") & "'}"
   End If

   '////// ESCAPE ANY PERIODS FOR SOME INTERNATIONAL DATES
   intIndex = InStr(strDate, ".")
   Do While intIndex
       strDate = Left(strDate, intIndex - 1) & "-" & _
                 Right(strDate, Len(strDate) - intIndex)
       intIndex = InStr(strDate, ".")
   Loop

   PrepDate = strDate
End Function

Happy Programming!

  • By Doug Dean


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