Compare current time to a separate date and time DB field

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if you want to compare by strings:

strDate = Format(Now, "dd-mmm-yyyy Hh:Nn:Ss")
msgbox strDate
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) ).
Anthony PerkinsCommented:
Try this:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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:~)

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.

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
Anthony PerkinsCommented:
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.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.