Dealing with dates in dd/mm/yyyy format

Question

Hi there.

I have a VB6 application which works with datetime values in SQL Server (which are obviously storing dates as mm/dd/yyyy).

I need to represent these dates to the user as dd/mm/yyyy, read them in as dd/mm/yyyy, and then store them back into the database as the standard mm/dd/yyyy.

This is the current code snippets I have which pull + insert the dates, however I have read many conflicting methods of handling the conversions, and I was wondering if anyone here knew a clear solution for this situation.

"SELECT * FROM List WHERE DateIn LIKE '%" & txtDateIn.Text & "%'"

"UPDATE [Progress] SET [Date] = '" & txtDate.Text & "'"

txtDate.Text = "" & RecordSet.Fields("Date").Value

Any thoughts? Thanks in advance.

**Update

Actually I just noticed I do have dates stored in datetime fields in the form of 16/08/2009 00:00:00 which is dd/mm/yyyy. So perhaps I misunderstood the problem. But when trying to update the datetime value I have been getting 'The conversion of char data type to a datetime data type resulted in an out-of-range datetime value.'.

I assumed this was because the date formats did not match (causing a problem with having a month value out of range) however I do have date values in the format of day/month/year in the datetime field already. And the date being submitted to the database is definitely dd/mm/yyyy.

** Update 2

Ok, there seems to be some confusion I have caused. I apologize.

• I am storing the dates as datetime in the SQL database
• The texts are TextBox controls in the VB6 application
• I am running SQL SELECT statements to read the dates from the database and place the value in a TextBox
• I then have a 'commit' command button which then performs an UPDATE SQL statement to place the value of the TextBox into the datetime field in the SQL database
• This works perfectly fine until 1 specific occasion.

In this occasion I have a datetime value (which SQL Server 2005 displays as 16/08/2009 00:00:00) which is read from the database and populated the TextBox with the value 16/08/2009. Now when I try to run the UPDATE statement without modifying the TextBox text I get the error 'The conversion of char data type to a datetime data type resulted in an out-of-range datetime value.'

This does not occur with other records such as one where the date is 04/08/2009 so the only issue I can see is possibly with the position of day and month in the value because if the DB is expecting month first then obviously 16/08/2009 would be out-of-range. However the value in the database is already 16/08/2009 with no issues.

Answer 1

SQL Server doesn't "obviously" store dates as mm/dd/yyyy. It doesn't store them in a text format at all, as far as I'm aware.

I don't know what the VB6 support for parameterised queries is, but that's what you want: basically you want to pass the argument to the query as a date rather than as text. Basically you should parse the user input into a date (in whatever way VB6 does this) and then pass it through in the paramterised query.

EDIT: I've tried to find out how VB6 handles parameterised queries, and not had a great deal of luck - hopefully any good book on VB6 will cover it. (There are loads of examples for VB.NET, of course...) There's a Wrox post which gives an example; that may be enough to get you going.

EDIT: As the comment to this answer and the edit to this question edit indicate, there's some confusion as to what your data types really are. Please don't use character-based fields to store dates: no good can come of that. Use a proper date/datetime/whatever field, and then make sure you use parameterised queries to access the database so that the driver can do any necessary conversions. Relying on a text format at all is a bad idea.

Answer 2

Use the ODBC Canonical form of the date or timestamp in your queries. This avoids any misunderstanding due to localization when storing the dates. The timestamp format is {ts 'yyyy-mm-dd hh:mm:ss[.fff]'} The date format is {d 'yyyy-mm-dd'} Here are the functions I use for this.

Now as for entry and display in VB6. Ideally you'd be using the datetimepicker control instead of a textbox as that returns a date and there is no misunderstanding when using it which date you are picking. But if you can assume it is always DD/MM/YYYY (that is a big if), you can use Format(datevalue, "DD/MM/YYYY") to display it. To read it into a date variable you can't just use CDate. You'll need to use parsing and something like DateSerial to put it together: DateSerial(Right(strDate, 4), Mid(strDate, 4, 2), Mid(strDate, 1, 2)).

' ------------------------------------------------------------------------------
' DateLiteral
'
' Description :
'    given a vb date, it returns a string with the odbc canonical date format.
'
' History
' 2008-02-04 - WSR : added to this project
'
Public Function DateLiteral(ByRef dtSource As Date) As String

    DateLiteral = _
        "{d '" & LeftPadDigits(Year(dtSource), 4) & "-" & _
                 LeftPadDigits(Month(dtSource), 2) & "-" & _
                 LeftPadDigits(Day(dtSource), 2) & "'}"

End Function
' ------------------------------------------------------------------------------


' ------------------------------------------------------------------------------
' TimeStampLiteral
'
' Description :
'    given a vb date, it returns a string with the odbc canonical timestamp format.
'
' History
' 2008-02-04 - WSR : added to this project
'
Public Function TimeStampLiteral(ByRef dtSource As Date) As String

    TimeStampLiteral = _
        "{ts '" & LeftPadDigits(Year(dtSource), 4) & "-" & _
                 LeftPadDigits(Month(dtSource), 2) & "-" & _
                 LeftPadDigits(Day(dtSource), 2) & " " & _
                 LeftPadDigits(Hour(dtSource), 2) & ":" & _
                 LeftPadDigits(Minute(dtSource), 2) & ":" & _
                 LeftPadDigits(Second(dtSource), 2) & "'}"

End Function
' ------------------------------------------------------------------------------


' ------------------------------------------------------------------------------
' LeftPadDigits
'
' Description : pads the given string to the left with zeroes if it is under
'    the given length so that it is at least as long as the given length.
'
' History
' 2008-02-04 - WSR : added to this project
'
Public Function LeftPadDigits(ByVal strSource As String, ByVal lngLength As Long) As String

    If Len(strSource) < lngLength Then
        LeftPadDigits = String$(lngLength - Len(strSource), "0") & strSource
    Else
        LeftPadDigits = strSource
    End If

End Function
' ------------------------------------------------------------------------------

Also should be noted, yes the first choice is to use ADO and parameterized queries. In my case I access the database through a third party library and can't use parameterized queries. Thus the date literal handling. Here is an example of the ADO parameterized queries though. The code can be different depending on which type of ADO connection you are using though: OLEDB, ODBC or SQLNative. Sometimes it isn't just a ? for the parameter marker. This example is OLEDB.

Set cmdVerifyUser = New ADODB.Command
cmdVerifyUser.CommandType = adCmdText
cmdVerifyUser.CommandTimeout = 30
cmdVerifyUser.CommandText = "SELECT username FROM users WHERE userid = ?"
cmdVerifyUser.Parameters.Append cmdVerifyUser.CreateParameter("userid", adVarChar, adParamInput, Len(m_strUserName), m_strUserName)
cmdVerifyUser.ActiveConnection = m_conDatabase
Set rstResults = cmdVerifyUser.Execute()

If Not rstResults.EOF Then

Answer 3

Well after all of that the problem was simple. I have the date value wrapped in single (') and double (") quotes. The problem was encountered due to date values not requiring the single quotes. Removing them solved the issue.

Thank you anyway for trying to help all.