Solved

calculating date difference..pls URGENT!!

Posted on 2002-07-27
25
275 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
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.

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

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

816 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

13 Experts available now in Live!

Get 1:1 Help Now