Solved

calculating date difference..pls URGENT!!

Posted on 2002-07-27
25
273 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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

12 Experts available now in Live!

Get 1:1 Help Now