Solved

# calculating date difference..pls URGENT!!

Posted on 2002-07-27
273 Views
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
Question by:Rajesh_Vijayan
• 10
• 7
• 6
• +2

LVL 33

Expert Comment

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

hongjun
0

LVL 3

Expert Comment

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

ID: 7181865
Check out the DateDiff function.

hongjun
0

LVL 33

Expert Comment

ID: 7181866
DateDiff function
http://www.devguru.com/Technologies/vbscript/quickref/datediff.html

hongjun
0

LVL 3

Accepted Solution

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

ID: 7181872
sanjaykattimani is giving a very good suggestion.

hongjun
0

LVL 3

Expert Comment

ID: 7181876
:p I want to get my first expert points...
0

LVL 33

Expert Comment

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

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

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

hongjun
0

LVL 33

Expert Comment

ID: 7181888
doesn't mean you are inadequate skilled!!! pls do not misunderstand.

hongjun
0

Author Comment

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

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

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

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

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

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

Author Comment

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

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

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

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

ID: 7183074
thanks for the suggestion
0

LVL 33

Expert Comment

ID: 7183076
Well done sanjaykattimani :)

hongjun
0

LVL 3

Expert Comment

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

ID: 7183083
:) Thanks guys.
0

## Featured Post

### 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â€¦