We help IT Professionals succeed at work.

SQL Syntax for Date

Medium Priority
359 Views
Last Modified: 2012-03-12
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
Comment
Watch Question

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

Commented:
are you trying to do some date calculations?

What data type is pDateEntered and MMColParam2?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

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

Author

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
Ess KayEntrapenuer
CERTIFIED EXPERT

Commented:
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
Top Expert 2011
Commented:
You try this assuming you want minutes and seconds as well.

WHEN convert(varchar,GETDATE()) - convert(varchar,pDateEntered)) < MMColParam2 THEN 1
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
???

That's not even a valid operation.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.