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

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

calculating date difference..pls URGENT!!

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
Rajesh_Vijayan
Asked:
Rajesh_Vijayan
  • 10
  • 7
  • 6
  • +2
1 Solution
 
hongjunCommented:
Why not store the values as datetime field? That will solve the problem totally.

hongjun
0
 
sanjaykattimaniCommented:
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
 
hongjunCommented:
Check out the DateDiff function.

hongjun
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hongjunCommented:
Forgot to paste link.
DateDiff function
http://www.devguru.com/Technologies/vbscript/quickref/datediff.html

hongjun
0
 
sanjaykattimaniCommented:
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
 
hongjunCommented:
sanjaykattimani is giving a very good suggestion.

hongjun
0
 
sanjaykattimaniCommented:
:p I want to get my first expert points...
0
 
hongjunCommented:
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
 
sanjaykattimaniCommented:
:) 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
 
hongjunCommented:
Ya that's why I put "newbies in answering questions".

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

hongjun
0
 
Rajesh_VijayanAuthor Commented:
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
 
hongjunCommented:
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
 
sanjaykattimaniCommented:
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
 
jayeshshahCommented:
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
 
hongjunCommented:
>>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
 
Rajesh_VijayanAuthor Commented:
Yeah , Iam inputting it thru a textbox.
0
 
Rajesh_VijayanAuthor Commented:
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
 
Rajesh_VijayanAuthor Commented:
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
 
Rajesh_VijayanAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Rajesh_VijayanAuthor Commented:
thanks for the suggestion
0
 
hongjunCommented:
Well done sanjaykattimani :)

hongjun
0
 
sanjaykattimaniCommented:
>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
 
sanjaykattimaniCommented:
:) Thanks guys.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 7
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now