• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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
0
grantballantyne
Asked:
grantballantyne
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

WHEN convert(varchar,GETDATE()) - convert(varchar,pDateEntered)) < MMColParam2 THEN 1
0
 
Scott PletcherSenior DBACommented:
???

That's not even a valid operation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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