SQL Syntax for Date

Dear Experts

I have a recordset within dreamweaver as follows:

SELECT *
FROM dbo.propertylistingresults_col
WHERE pPrice <= MMColParam and pPrice >= MMColParam1 and (GETDATE() - pDateEntered) < MMColParam2

However when trying to display the page I receive the following error message:



Microsoft OLE DB Provider for ODBC Drivers

 error '80040e57'

[Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting expression to data type datetime.
 
/propertyresults_quick.asp, line 37


Can anyone advise me how to amend the necessery within the recordset SQL query to eliminate this.

Thanks
grantballantyneAsked:
Who is Participating?
 
sammySeltzerConnect With a Mentor Commented:
You try this assuming you want minutes and seconds as well.

WHEN convert(varchar,GETDATE()) - convert(varchar,pDateEntered)) < MMColParam2 THEN 1
0
 
venk_rCommented:
Please use Datediff or Dataddd function instead of (GETDATE() - pDateEntered) for comparison.
Its always safe to use them.
0
 
sammySeltzerCommented:
are you trying to do some date calculations?

What data type is pDateEntered and MMColParam2?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Scott PletcherSenior DBACommented:
You've got a value in "pDateEntered" that is not a valid datetime.


SELECT *
FROM dbo.propertylistingresults_col
WHERE
    pPrice <= MMColParam AND
    pPrice >= MMColParam1 AND
    1 = CASE
        WHEN ISDATE(pDateEntered) = 0 THEN 0
        WHEN (GETDATE() - pDateEntered) < MMColParam2 THEN 1
        ELSE 0 END
0
 
grantballantyneAuthor Commented:
Hi

pDateEntered is Date and MMColparam2 is numeric.

What I am trying to calculate is the number of days between the to dates to a numeric figure and then return the records where the result is < MMColParam2

Thanks
0
 
grantballantyneAuthor Commented:
Hi

I tried the syntax above but still get the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting expression to data type datetime.

/propertyresults_quick.asp, line 37



Thanks
0
 
Ess KayEntrapenuerCommented:
use cast or convert to varchar, and the trim the line to the first 10 letters

instead of the full dat 10/10/2012 01:30:33 it will just be 10/10/2012

read more here

http://www.sqlusa.com/bestpractices/datetimeconversion/
http://linesofcode.net/snippets/45
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
http://www.w3schools.com/sql/sql_dates.asp
0
 
Scott PletcherSenior DBACommented:
???

That's not even a valid operation.
0
All Courses

From novice to tech pro — start learning today.