dsgvwf
asked on
Problem with decimal portion of seconds in date
I am working on a VB6 - ADO program that ties in with some stored proceedures (developed by a third party). The problem that I am running into is that when the stored proceedure populates a datetime field it records the seconds to 3 decimal points, for example 09/01/2008 09:29:25.233. When I use visual basic to retrieve this field and then try to run a query using the result it seems like it is cutting off the decimal portion of the seconds and giving me an empty record set.
example
SQL Server table:
Field1 ModifyDateTime
Test 09/01/08 09:29:25.233
Using VB6 and an ADO Query
sqltext = "Select * from myTable where field1 = 'Test'"
myRS.open sqltext, myConnection, adOpenDynamic, adLockOptimistic
#At this point one record is returned, so I note the modify datetime
tmpdte = myRS!ModifyDateTime
#now we run a query using the returned value
sqltext = "Select * from myTable where ModifyDateTime = '" & tmpdte & "'"
myRS2.open sqltext, myConnection, adOpenDynamic, adLockOptimistic
#this returns no records, however if I change the value in the modifydatetime field to 09/01/08 09:29:25 then it works fine.
Does anyone have any ideas how to get around this.
example
SQL Server table:
Field1 ModifyDateTime
Test 09/01/08 09:29:25.233
Using VB6 and an ADO Query
sqltext = "Select * from myTable where field1 = 'Test'"
myRS.open sqltext, myConnection, adOpenDynamic, adLockOptimistic
#At this point one record is returned, so I note the modify datetime
tmpdte = myRS!ModifyDateTime
#now we run a query using the returned value
sqltext = "Select * from myTable where ModifyDateTime = '" & tmpdte & "'"
myRS2.open sqltext, myConnection, adOpenDynamic, adLockOptimistic
#this returns no records, however if I change the value in the modifydatetime field to 09/01/08 09:29:25 then it works fine.
Does anyone have any ideas how to get around this.
This will use DateDiff (http://msdn.microsoft.com/en-us/library/ms189794.aspx) to check the difference in the times in seconds. This should yield 0 between 9:25:00.000 and 9:25:00.233.
Hope that helps.
Hope that helps.
ASKER
My VB6 routine will be making use of several stored proceedures to update records in the database that will depend on matching the datetime fields exactly. Is there anyway to adjust the precision in VB6 to match the milliseconds?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I have found a work around.
When get the returned modifydatetime I convert it to a double using CDbl then use this to calcualte the millisecond portion, and build a string for the date instead of using a date type. When I create the parameter for my stored proceedure, I declare it as a adVarChar instead of adDBTimeStamp and it seems to run ok. I assume the variable is being forced into a date type on the server side.
It is not the most elegant solution but it seems to work.
Thanks for your help, even though you didn't provide the exact answer I was looking for, you gave me a direction I had not thought to go in.
When get the returned modifydatetime I convert it to a double using CDbl then use this to calcualte the millisecond portion, and build a string for the date instead of using a date type. When I create the parameter for my stored proceedure, I declare it as a adVarChar instead of adDBTimeStamp and it seems to run ok. I assume the variable is being forced into a date type on the server side.
It is not the most elegant solution but it seems to work.
Thanks for your help, even though you didn't provide the exact answer I was looking for, you gave me a direction I had not thought to go in.
It sure does. Meant to post that, but I didn't so :(.
What I had thought was you could add a field onto your original query to get the ModifyDateTime that gets the millisecond part. You can use DatePart(ms, ModifyDateTime) then you can just concatenate it to the end of your date. If it makes it easier. May be same amount of effort.
http://msdn.microsoft.com/en-us/library/ms174420.aspx
What I had thought was you could add a field onto your original query to get the ModifyDateTime that gets the millisecond part. You can use DatePart(ms, ModifyDateTime) then you can just concatenate it to the end of your date. If it makes it easier. May be same amount of effort.
http://msdn.microsoft.com/en-us/library/ms174420.aspx
Open in new window