Solved

SQL Syntax for Date

Posted on 2012-03-12
8
315 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
0
Comment
Question by:grantballantyne
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 8

Expert Comment

by:venk_r
Comment Utility
Please use Datediff or Dataddd function instead of (GETDATE() - pDateEntered) for comparison.
Its always safe to use them.
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
are you trying to do some date calculations?

What data type is pDateEntered and MMColParam2?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:grantballantyne
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:grantballantyne
Comment Utility
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
 
LVL 15

Expert Comment

by:Ess Kay
Comment Utility
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
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
Comment Utility
You try this assuming you want minutes and seconds as well.

WHEN convert(varchar,GETDATE()) - convert(varchar,pDateEntered)) < MMColParam2 THEN 1
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
???

That's not even a valid operation.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now