asked on

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

venk_r

Please use Datediff or Dataddd function instead of (GETDATE() - pDateEntered) for comparison.
Its always safe to use them.

sammySeltzer

are you trying to do some date calculations?

What data type is pDateEntered and MMColParam2?

Scott Pletcher

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

grantballantyne

ASKER

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

grantballantyne

ASKER

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

Ess Kay

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

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

???

That's not even a valid operation.