Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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?
0
jjkempel
Asked:
jjkempel
1 Solution
 
bobbit31Commented:
if you want to compare by strings:

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

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


Anthony
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
fsaimsCommented:
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
 
inthedarkCommented:
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
 
inthedarkCommented:
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
 
hsboyCommented:
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
 
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:
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
 
planoczCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now