Handling Dates in SQL Server <--> VB6
Posted on 2006-06-05
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)
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?