Solved

calculating date difference..pls URGENT!!

Posted on 2002-07-27
25
274 Views
Last Modified: 2010-05-02
I have a form with a field called visit date.
When user (a person who has already registered)comes again to register,i should check the visit date with the earlier visit date(which is stored in the database). if date difference is 7 then i should apply a scheme for the person, But iam getting error when i calculate the date difference.Couls anyone help me to write a proper query,right now i get the error which is something like: converting string to date time value resulted in an outof range date time value.
pls.. its very urgent
rgds and thanks..
0
Comment
Question by:Rajesh_Vijayan
  • 10
  • 7
  • 6
  • +2
25 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 7181861
Why not store the values as datetime field? That will solve the problem totally.

hongjun
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7181862
Depends on the way you have stored date. if it is in following format it displays difference as 7

MsgBox DateDiff("d", "20-jul-2002", Now)

Hope it helps
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181865
Check out the DateDiff function.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181866
Forgot to paste link.
DateDiff function
http://www.devguru.com/Technologies/vbscript/quickref/datediff.html

hongjun
0
 
LVL 3

Accepted Solution

by:
sanjaykattimani earned 100 total points
ID: 7181869
1)If you are using sql server as back end you can use following query to get the date difference
select datediff(day,DATEOFJOINING,getdate()) from customers
2) if using oracle following query will fetch you date difference
select round(sysdate-DATEOFJOINING) from  customers
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181872
sanjaykattimani is giving a very good suggestion.

hongjun
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7181876
:p I want to get my first expert points...
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181879
sanjaykattimani, welcome on board. Usually newbies in answering questions always go around proposing their comments as answers and thus locking the questions but then you prove to be professional offerring alternatives/solutions leaving it open :)

hongjun
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7181886
:) infact i m not a newbie neither for EE. I have been asking lot of questions, but never puting back salt in to sea ;)
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181887
Ya that's why I put "newbies in answering questions".

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181888
>>"newbies in answering questions"
doesn't mean you are inadequate skilled!!! pls do not misunderstand.

hongjun
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7181894
thanks for the suggestions.
but i have only one date visit date in database, the other date is from the front end screen.
so i cannot use getDate() function,right?
so it is comparing a database value with the current
screen value and then applying the scheme.
pls. could u lok into this...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 33

Expert Comment

by:hongjun
ID: 7181897
So it would be something like this. I assume rs("visitdate") is the field value and rs is your recordset.

' date difference is 7
if ( DateDiff("d", rs("visitdate"), Now()) = 7 ) then
' do something
elseif ( DateDiff("d", rs("visitdate"), Now()) > 7 ) then
' do something
end if



hongjun
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7181901
This query can be used to feed the date from front end (2002-jul-27) and get the difference in recordset.

select datediff(day,dateofbirth,'2002-jul-27') from distributors


Hope it helps.
0
 
LVL 5

Expert Comment

by:jayeshshah
ID: 7181903
Open a recordset which shall calculate the difference.

Rs.open "select datediff(d,visitdate," & cdate(now) & ") from tablename"

if rs(0) = 7 then
  --- Apply a scheme
end if
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7181904
>>the other date is from the front end screen
If that field is to be entered by the user, perhaps you input it. textbox, selectbox, or ???

hongjun
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7181988
Yeah , Iam inputting it thru a textbox.
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7181991
so it should have to be something like:
datediff(d,visitdate{this is from database},txtvisitdate{this is from front screen})=7
but this is giving error
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7181995
so it should have to be something like:
datediff(d,visitdate{this is from database},txtvisitdate{this is from front screen})=7
but this is giving error
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7182003
so it should have to be something like:
datediff(d,visitdate{this is from database},txtvisitdate{this is from front screen})=7
but this is giving error
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7182016
Duplicate question see:
http://www.experts-exchange.com/mssql/Q_20328744.html

Also, please maintain these open questions:
Tabbing through flexgrids Date: 03/30/2002 11:14PM PST
http://www.experts-exchange.com/visualbasic/Q_20283367.html
DoEvents?? Date: 03/23/2002 11:55PM PST
http://www.experts-exchange.com/visualbasic/Q_20280832.html

Thanks,
Anthony
0
 

Author Comment

by:Rajesh_Vijayan
ID: 7183074
thanks for the suggestion
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7183076
Well done sanjaykattimani :)

hongjun
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7183082
>select datediff(day,dateofbirth,'2002-jul-27') from distributors

You could put the form variables in the above query to get the output. You need to create the date from front end variables.

1)If you are using ASP as front end create 3 combo boxes to accept day part, month part and year part [this is to avoid in valid date formats entry.]
and use the query as follows

SqlQry="select datediff(day,visitdate ,'" & request("Year") & "-" & request("Month") & "-" & request("Day") & "') from visitors"

2) If you have only one textbox where you accept date, ask the user to enter date in the fixed format and use it as below.
SqlQry="select datediff(day,visitdate ,'" & request("TodaysDate") & "') from visitors"

This creates query required to fetch date difference.

Also refreshing the page on EE resubmits the question or answer, Therefore Reload question is provided on top.
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7183083
:) Thanks guys.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Basic Excel Formatting error 4 83
VB6 Compile Compatibility Issue 4 90
Adding to a VBA? 6 61
Using an encrypted  value to decrypt and display contents in vb6 9 49
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

18 Experts available now in Live!

Get 1:1 Help Now