[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

Handling Dates in SQL Server <--> VB6

The answer is probably as clear as day, but I am pulling my hair out over it. I have a SQL Server 2000 database that I am interfacing against with VB6. There are a lot of tables with SmallDateTime date formats. When I run a query, for date fields with "no" value (I use the term loosely) I get differing results. Somehow, a lot of the fields that should be null, have a value of 1/1/1900 in them. Of course, coming back into VB, I get a value of 1/1/1900. I can use ISNULL(date, '') to handle null fields, but I tried using ISNULL(NULLIF(date, '1/1/1900'), '') but it does not work. I have to use Convert and check against the string '1/1/1900'

Then of course, VB's default empty date is altogether different yet.

I have seen the code:

Public Property Let DateAcquired(Value As Variant)
  If Len(Trim(Value)) = 0 Then
    mudtProps.DateAcquired = ""
  ElseIf IsDate(Value) Then
    mudtProps.DateAcquired = CVDate(Value)
  End If
End Property

mudtProps.DateAcquired is a Date variable. However, when I try to set a Date variable to an empty string, I get a Type Mismatch (VB Error Code 13).

Does anyone have any consistent way of handling dates between VB and SQL?
0
dbbishop
Asked:
dbbishop
  • 5
  • 4
  • 3
  • +2
2 Solutions
 
iHadiCommented:
Hi dbbishop
First of all SQL Server returns a NULL if the field of the date is not set to a value and you'll get an error when trying to get the value of a field that is set to null

Ex:
Dim MyVar as Date
MyVar = Recordset.Fields("Date").Value ' An error if the field is a null

And to compare a date if it is null se the following url:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21873799.html
0
 
junglerover77Commented:
In fact, you can just treat the "date" data type in VB as double.

Try the following statements in the immediate window, and then you will understand what on hell "date" is:

?format(cdate(0),"yyyy-MM-dd hh:mm:ss")
?format(cdate(2),"yyyy-MM-dd hh:mm:ss")
?format(cdate(-1),"yyyy-MM-dd hh:mm:ss")
?format(cdate(2.5),"yyyy-MM-dd hh:mm:ss")

So, I suggest you to change your code like this:

Public Property Let DateAcquired(Value As Variant)
  If Len(Trim(Value)) = 0 Then
    mudtProps.DateAcquired = 0
  ElseIf IsDate(Value) Then
    mudtProps.DateAcquired = CDate(Value)
  End If
End Property

And whenever you wanna assign a null value to a date column, just assign ZERO to it.

Regards,
Jungle
0
 
dbbishopAuthor Commented:
That is why I was using ISNULL(). The problem is that between 12/30/1899, 1/1/1900, empty strings, nulls... I was wondering if anyone has come up with an easy-to-use catchall function to handle. The sample code I show above is from Business Objects with VB6.

I am wondering how he is getting it to work (setting a Date to an empty string), when I get a Type Mismatch.

I can use the following to return an empty string, but I cannot set it to a date. I do not want to use a variant for the date, as I am using his example of passing UDTs and need to preserve the data in a predetermined length UDT.

ISNULL(CONVERT(NVARCHAR(10), E.CurrPosStartDate, 101), '')
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
inthedarkCommented:
The other problem that you have to overcome is that SQL Server behaves diffenetly with depending ok the SQL user's language.  So it is important to get it right. junglerover77's is close to a good answer. But if you read VB's format command help and see the section "User-Defined Date/Time Formats (Format Function)" the correct date format for SQL should be "yyyy-mm-dd Hh:Nn:Ss"

But you have only found a problem with dates, but tomorrow you will  find problems with many other data types. I created a simple class which handles all data types also depending on if you are connecting to SQL Server, or Access database. I could post an extract of this class if you wish.  It also shows hoe to somply handle if a date value is zero or null.
0
 
inthedarkCommented:
You will also need to know how to select records based on a range of dates.
0
 
lojk.Net and Infrastructure ConsultantCommented:
I have had many (heated) discussions with various developers over this issue for years and years...

SQL Default Date Acceptance is actually mm-dd-yyyy unless you changed the settings during install

In many situations (especially those where you dont actually know whether the  container for your data will be SQL or Access) it is actually better to store dates as strings  and format your dates (on the way in) to yyyyMMdd (yyyyMMddhhmmss). this still allows for sorting and between functions on dates because they will always be stored in chronological order (and are easiily broken down again into a standard date as needed on the way out)

It is a fudge but once adopted it provides the greatest degree of flexibility. Of course the other real option is to (as jungleover says) to convert to double and store the number but then that requires much more processing on accessing the number as a date later.
0
 
lojk.Net and Infrastructure ConsultantCommented:
oh, BTW....

        Public Shared Function CheckNullString(ByVal stringToCheck As Object) As String
                Dim tret As String = ""
                Try
                        tret = CStr(stringToCheck)
                Catch ex As Exception

                End Try
                Return tret
        End Function
0
 
lojk.Net and Infrastructure ConsultantCommented:
Sorry for VB6....

        Public Function CheckNullString(ByVal stringToCheck  ) As String
                Dim tret As String
                on error resume next
                        tret = CStr(stringToCheck)
                CheckNullString=tret
        End Function
0
 
dbbishopAuthor Commented:
I guess one of the real problems is that I have two possible scenarios within SQL Server when I do not have a date. Depending on what previous business logic has done to handle a table update when one of the fields is a date and there was no date, I can either have a NULL (actually preferred) or 1900-01-01 00:00:00., which then needs to be handled differently. So far, the way I've come up with handling it so I get consistent values is with the following:

select case isnull(hire_date, '') when '' then '0' when '1900-01-01' then '0' else convert(nvarchar(12), hire_date, 101) end

I have to use the convert, otherwise SQL will attempt to coerce the '0's into dates and produces an error. If I change '0' to 0 (numeric) then the 0 is coerced back to a date and goes back to 1900-01-01. I will probably make it a function that I can implement in my code unless someone has a better idea. I think it will be more efficient than trying to do it all in VB.
0
 
inthedarkCommented:
Getting a date out of a recordset is easy, can be done with GitNum() (or use Git() for strings)

When loading your UDT with date data types:

MyUDT.MyDate = GitNum(RS("MyDateField")) ' Turns Nulls into zeros

To put back into an sql statement:

SQL = "Update [ MyTable]"
SQL = SQL +" Set MyDateField=" + cSQLAny(MyUDT.MyDate, "D") ' date example Turns zero into null
SQL = SQL +",  MyBoolean=" + cSQLAny(MyUDT.MyBool, "B") ' date example Turns zero into null
etc.
SQL = SQL +" Where ([MyKey]="+cSQLAny(KeyTextData, "T")+")"



Example function aslo show how to correctly handle boolean, numeric and text fields.

See cSQLAny code for data type info

Hope examples help you:~)

Functions:

Public Function GitNum(RSField)

' Force a null field to be numeric

If IsNull(RSField) Then
    GitNum = 0
Else
    GitNum = RSField
End If

End Function


Public Function Git(RSField)

' Force a null field to be blank string

If IsNull(RSField) Then
    GitNum = ""
Else
    GitNum = RSField
End If

End Function

Function cSQLAny(Value, DataType) As String

' Converts Value into a valid SQL parameter for adding to SQL statements
' Types: B=Boolean, D=Date, N=Numeric, [T=Text, M=Memo]
Select Case UCase(Left(DataType, 1))
    Case Is = "B"
        cSQLAny = cSQLBoolean(Value)
    Case Is = "D"
        If IsNull(Value) Then
            cSQLAny = "Null"
        Else
            ' if date is blank or zero make it nulls
            If Len(CStr(Value)) > 0 And Value <> 0 Then
                If DBType = "MDB" Then
                    cSQLAny = "#" + cSQLDate(Value) + "#"
                Else
                    cSQLAny = "'" + cSQLDate(Value) + "'"
                End If
            Else
                cSQLAny = "Null"
            End If
        End If
    Case Is = "N"
        If IsNull(Value) Then
            cSQLAny = "Null"
        Else
            cSQLAny = CStr(Value)
            If Len(cSQLAny) = 0 Then
                cSQLAny = "0"
            End If
            cSQLAny = Replace(cSQLAny, ",", "")
        End If
       
    Case Else
        If IsNull(Value) Then
            cSQLAny = "Null"
        Else

            cSQLAny = "'" + cSQL(Git(Value)) + "'"
        End If
End Select

End Function

Public Function cSQL(ByRef SQLData) As String

' Handle possible ' in a string like O'Connor

If IsNull(SQLData) Then
    cSQL = ""
Else
    cSQL = Replace(CStr(SQLData), "'", "''")
End If
End Function

Function cSQLDate(DatePassed)

' converts a date for use in an SQL

'example:
'strWhere = "MyDate = '" + ADO.cSQLDate(Now) + "'"

' Needs Dim mCurrentDateFormat as String ' in module decs.
If Len(mCurrentDateFormat) = 0 Then
    ' format of date depends on coutry of usage so this may need change
    mCurrentDateFormat = "yyyy-mm-dd Hh:Nn:Ss"
End If

cSQLDate = Format$(DatePassed, mCurrentDateFormat)

'When specifying dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same for all language settings:
'ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:
'{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
'{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
'{ t 'hh:mm:ss'} such as: { t '10:02:20'}

End Function
Public Function cSQLBoolean(StringBoolean) As String

' Returns 1 or 0 for and SQL Update string
' Works with boolean or string values.

' examples:

'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(Request("MyCheckBox")) ' web page checkbox post
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(RS("BooleanField")) ' Recordset
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean("Yes")) ' String ' Yes/No Oui/Non True/False
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(True) ' Boolean values

If IsNull(StringBoolean) Then
    cSQLBoolean = "0"
    Exit Function
End If
If TypeOf StringBoolean Is CheckBox Then
    If StringBoolean.Value = 1 Then
        cSQLBoolean = "1"
    Else
        cSQLBoolean = "0"
    End If
Else
    ' Yes/No Oui/Non True/False
    If UCase$(Left(CStr(StringBoolean), 1)) = "T" Or UCase(Left(CStr(StringBoolean), 1)) = "Y" Or UCase(Left(CStr(StringBoolean), 1)) = "O" Then
        cSQLBoolean = "1"
    Else
        cSQLBoolean = "0"
    End If
End If
End Function
Public Function cSQLTime(tv As Date) As String

' Formats a time value for an SQL statement
Dim a$
a$ = App.EXEName
cSQLTime = Format(tv, "Hh:Nn:Ss")
End Function
0
 
dbbishopAuthor Commented:
Thanks to everyone for the help. The biggest problem is not handling nulls (that is SIMPLE to do in SQL with ISNULL() and NULLIF(). The headache I was having was when a zero or empty string was written back to the database from VB (or whatever app). Then, the null became 1900-1-1 00:00:00, which is not null, and is a valid date. It was easiest to add a user function to SQL since I make ALL of my database calls through stored procedures, to handle the problem. In case anyone is interested, here is the function:

CREATE function fmt_date (@Date SmallDateTime)
returns nvarchar(10)
as
    begin
      return case isnull(@Date, '') when '' then '0' when '1900-01-01' then '0' else convert(nvarchar(12), @Date, 101) end
    end

I had to hose it up a little because the case statement won't work properly if I say case @Date when null then '0' else convert(nvarchar(12), @Date, 101) end

To use just code it as a function call in your T-SQL code:

SELECT LastName, FirstName, dbo.fmt_date(HireDate) FROM myTable

If the date in the database is null or 1900-1-1, it will return 0, otherwise it reurns the date (the 101 in T-SQL formats it as mm/dd/yyyy).
0
 
inthedarkCommented:
1) First change the code that maintains this table to write null instead of zero
2) Run an update query to change zero dates to null. (If you have multiple date fields, run the query for each field.)

0
 
dbbishopAuthor Commented:
Wow. A lot of opinions and some decent suggestions. I have gone mostly with the function I show above plus a cleanup of the database. I have found out that some of the IT staff go directly into the databases with Enterprise Manager, so I can not expect my business rules to always be enforced properly. The biggest problem, of course, is with dates that can be null.

Between changes I;ve made in the business logic, stored procedures, database cleanup, and the above function, hopefully things will go cleaner in the future.
0
 
inthedarkCommented:
You can use triggers to fix the data after a record is amended or a better solution is to set up a stored procedure which includes the update quesries which convert zero dates to nulls.  Before you start running any reports or queries, just fire up the stored procedure.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now