?
Solved

Problem with decimal portion of seconds in date

Posted on 2008-10-27
6
Medium Priority
?
302 Views
Last Modified: 2010-08-05
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.

0
Comment
Question by:dsgvwf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22813734
Try this as the decimal portion is number of milliseconds.  What is likely happening is your date time is only to seconds precision and so not able to match directly to the data in table at 9:25:00.000 doesn't equal 9:25:00.233.
sqltext = "Select * from myTable where DateDiff(s, ModifyDateTime, '" & tmpdte & "') = 0"

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22813746
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.
0
 

Author Comment

by:dsgvwf
ID: 22814011
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?
0
Technology Partners: 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!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 750 total points
ID: 22815330
It has been a while for straight VB6 and not .NET, but in VB6 I think the DateAdd function only takes iintervals down to s for second, but you can see if there is a millisecond portion that I am missing.  You could just add appropriate milliseconds to your code.  I don't know of a way though as everything for VB6 I see only goes to the second.  VB.NET has functionality if you can use that.

Otherwise, you will have do it on the database side.  In which case you can create a User Defined Function that returns a bit indicating if a datetime matches down to seconds then you can use the udf in code everywhere you need it.
0
 

Author Comment

by:dsgvwf
ID: 22815445
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22815600
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
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question