Solved

Compare current time to a separate date and time DB field

Posted on 2003-10-24
10
475 Views
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?
0
Comment
Question by:jjkempel
10 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 9616937
if you want to compare by strings:

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

Expert Comment

by:Dang123
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) ).
0
 
LVL 75

Accepted Solution

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

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


Anthony
0
 
LVL 1

Expert Comment

by:fsaims
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
else
debug.print false
end if

Best of Luck
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Expert Comment

by:inthedark
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
    DoEvents
Loop

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
Loop

' 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
Loop

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

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:
Hour(Now)
Minute(Now)
Second(Now)
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 this....you have 2 operators saving data into a database, one system is set of use datesm one system is set to uk dates...you 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)
    sDateFormat="UK"
Else
    sDateFormat="USA"
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:~)
0
 
LVL 17

Expert Comment

by:inthedark
ID: 9618586
DAO

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.

ADO

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.



0
 

Expert Comment

by:hsboy
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
0
 
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:
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20624445.html

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.

Thanks,
Anthony
0
 
LVL 27

Expert Comment

by:planocz
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now