Compare current time to a separate date and time DB field

Posted on 2003-10-24
Last Modified: 2010-05-01
I am struggling with something in VB, here is a summary of what I am having trouble with.
I retrieve 2 separate date and time fields from a database, and the date is in the format 17-OCT-2003 (for example) and the time is military format 09:30 (example).
I need to compare this time against the current system time inside my VB 6 application, and then move a file from one directory
to another if the current data and time is >= the time and date I got from the database.  I am not finding anything yet
in VB docs on how to get different components of the system time, the only thing I found was some constant "NOW", which
gives 1 long date time stamp.  Is there something that can give me the date in this format, and also the time in military time
that will allow me to compare the 2?
Question by:jjkempel
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
LVL 18

Expert Comment

ID: 9616937
if you want to compare by strings:

strDate = Format(Now, "dd-mmm-yyyy Hh:Nn:Ss")
msgbox strDate

Expert Comment

ID: 9617070
I think you should be able to compare your database field to Now. If you have trouble you could alwayse define two variables of type Date set one equal to your database value and the other equal to now. (Actually you should store the value of Now at the start of your routine so all your records are compared to the same date/time value (Now represents the clock time and so would change as your program loops through the records in your recordset) ).
LVL 75

Accepted Solution

Anthony Perkins earned 500 total points
ID: 9617100
Try this:

If Now() >= CDate(DateCol + " " + TimeCol)  Then
   'move a file from one directory to another

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!


Expert Comment

ID: 9617215
Hi Buddy this code will work perfectly for you if you could get date and time values as string from database

If Now() >= CDate(DateValue("17-OCT-2003") & " " & TimeValue("09:30")) Then
' move files to another directory
debug.print true
debug.print false
end if

Best of Luck
LVL 17

Expert Comment

ID: 9618551
Hi jjkempel, if you want to know all about dates and times here it is:

The system stores dates & times in a special data type know as Date example:

Dim dtMydate as  Date

dtMyDate = Now

The date data type is kind of like a numeric double.  The interger part is the number of days since 31/12/1899.  The decimal part is a fraction of one day.

MsgBox Format(Now + 1.5, "dd/mm/yy hh:nn:ss") ' adds 1 day and 12 hours

So if you take a file date you can seperate the date and time like this:

dim dtFiledate as Date

dtFileDate = FileDateTime("C:\MyFolder\MyFile.txt")

Dim dtDate as Date
Dim dtTime as Date

dtDate = Int(dtFileDate)  ' this would return the date part of the date
dtTime = dtFileTime - dtDate ' this would get just the hours and minutes

So consider this, a common problem is how to compare dates and times....say for example you want to wait for 10 secconds. You may think that this is easy but many progarmmers get screwed by this and tevery works find until midnight then gets a bug.

Const OneSecond as Double = 1#/(24#*60#*60#)

dtWaitUntill = Now + OneSecond * 10

Do Until Now > dtWaitUntil

So handy constants to have declared are:

Const OneSecond as Double = 1#/(24#*60#*60#)
Const OneMinute as Double = 1#/(24#*60#)
Const OneHour as Double = 1#/24#

Whe you are working with files you must be aware of a bug in VB's Dir() function.  If you intend to move files you code may work for a few days or weeks but will fail.  To avoice problems you must extract the contents of the older first before you start to move/delete any files. Say you want to move all files older than 3 hours:

' This will cause problems one day
sFile = Dir("C:\MyFolder\*.*)
Do While Len(sFfile) > 0
    If FiledateTime("C:\MyFolder\" + sFile) > Now + OneHour * 3 Then
         Name "C:\MyFolder\" + sFile As "C:\MyOtherFolder\" + sFile
    End If
   sFile = Dir

' This wont cause problems if you get the older contents before you
' process any files.

Dim colMyFiles as Collection
set colMyFiles as New Collection
sFile = Dir("C:\MyFolder\*.*)
Do While Len(sFfile) > 0
    If FiledateTime("C:\MyFolder\" + sFile) > Now + OneHour * 3 Then
         colMyFiles.Add sFile
    End If
   sFile = Dir

Dim vFile
For Each vFile in colMyFiles
    Name "C:\MyFolder\" + vFile As "C:\MyOtherFolder\" + vFile

In so far as Dates/Times, there are a number of functions you need to make friends with:

Date - returns the current date
Time - current time
Now - returns date + time
Timer - returns seconds passed midnight
DatePart(xx, Mydate) returns any part of a date and time eg. datePart("yyyy", Now) returns the current year
DateSerial(2003, 10, 23) returns a date made up from year, month and day.
Format (Anydate, "dddd") gets the current day
Format (Anydate, "ddd") gets the current day abriviated
Format (Anydate, "dd/mm/yyyy hh:nn:ss") notice that minutes are n and not m
You also have:
DateValue(sMyString) converts a string date into a numeric date
TimeValue(sMyString) converts a string time to a numeric time (a fraction of one day)

Above all remember that a date/time value is just a numeric double with style.

Warning: Is doe not matter that your software may never be used internationally, you can never assume that DateValue() will work in the way you intend.  You must take steps to prevent a disaster.  Consider have 2 operators saving data into a database, one system is set of use datesm one system is set to uk end up witg junk data.  Every works fine until the 13ths day of the month, then you get to find out that several thousand thransaction need to be coeeected because person made a minor error setting up a PC's regional settings.

So to stop all problems dead when your program starts up do this:

If DateValue("9/11/2001") = DateSerial(2001, 9, 11)
End If

You now need to compare sDateFormat with a value stored in a file to see the person has configered their system correctly.

If sDateFormat <> OfficeDefault Then
    MsgBox "Your PC is not setup correctly for operation in this office"
End If

There is much more to know about dates and time values, if you have any questions just ask!

Hope this helps:~)
LVL 17

Expert Comment

ID: 9618586

Working with dates in databases is a different subject.  But now you have a grounding knowledge of dates you can see why an sql statement like this won't work...

Select * from MyTable  Where MyDateField Is Between #1/1/2003# And #12/31/2003#

You could be forgiven for thinking that this would return all transactions within the current year, but it won't becuase any transaction entered after 30th December 2003 will be missing. To avoice this common mistake do it this way...

Select * From MyTable  Where MyDateField >=#1/1/2003# And MyDateField < #1/1/2004#

If you build your data using the Date() function, it will work.  But if at some stage somebody makes a minor change to the code cand replaces date with Now your queries may now stop working.

If your code is being maintained by an international team it is sometimes better to use DateSerial in your Where statements.


SQL in ADO is simpler to use as all dates MUST be entered in this format:

yyyy-mm-dd hh:nn:ss

If you use any other format, in SQL server apps, one operator who is given the wrong locale setting, can agian be entering crap for the first 12 days of the month before anybody discovers the problem.


Expert Comment

ID: 9620203
Suppose you declared two variables that store the date and time value you retrieved from the database:theDate and theTime.
Our tasks are:
1) join theDate and theTime
    Dim theDateTime as Date
    theDateTime=DateValue(Format("dd/mm/yyyy",theDate) & " " & Format("hh:nn:ss",theTime))

2) Compare the system date&time to the above value
    If DateDiff("s",Now,theDateTime)>0 then
        'It seems  theDateTime is later than Now
    End if
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9620243
Now that you have all this feedback in the form of solutions, the way it works here at EE is that you decide which is the most appropriate answer and award the points.  This way the question get's closed and we can all move on.

I mention this, because you have asked 2 questions and you have not yet closed the previous one from back in May:

You have to appreciate that there has been considerable well-intentioned effort involved on everyone's part, now we ask that you do your part as well.

LVL 27

Expert Comment

ID: 10183553
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from jjkempel from 10/25/2003 comment
Award points to acperkins is recommend.
Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

628 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