SQL query by Date - Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL query by Date - Problems

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-08 : 11:58:08

Hi All,

How can I retrieve the data based on the date. I need to retrieve all the records newer than the date 2003-09-30;

I used

SELECT * FROM vw_Absence_Report WHERE StartDate > '2003-09-30';

But doesnt work:

I get this error message:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'Where'.
/departments/human_resources/employee_absence/admin/reportgenerator.asp, line 61

The StartDate column in the SQL Server table has the values of the form

YYYY-MM-DD 00:00:00:000. I mean it’s a datetime field.

I also tried other variations of SQL query like:

SELECT * FROM vw_Absence_Report WHERE StartDate > #2003-09-30#;

SELECT * FROM vw_Absence_Report WHERE StartDate > '2003-09-30 00:00:00.000';

SELECT * FROM vw_Absence_Report WHERE StartDate > #2003-09-30 00:00:00.000#;

But none of them worked.

Any suggestions??

VJ

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 12:06:53

quote:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'Where'.
/departments/human_resources/employee_absence/admin/reportgenerator.asp, line 61

I'd say there's a problem with asp code...and it's probably right before or after the SELECT.

Cut and paste your first SELECT in QA..bet it runs

Try this in QA


USE Northwind
GO

SELECT * FROM Orders WHERE ShippedDate > '1997-09-30';
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-08 : 12:06:59

try...

SET DATEFORMAY YMD
SELECT * FROM vw_Absence_Report WHERE StartDate > '2003-09-30'

the default date format is MDY (i.e. USofA)

Search here for this topic...it's come up loads of times.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 12:09:09

BUHYAH

6 Seconds!

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-08 : 13:05:21

Actually "vw_Absence_Report" is a view formed using other tables in the database. Does this might be causing any problems that i am facing. I mean can ASP SQL reference views as it does with the tables.

Any suggestions.

Thanks

Vijay

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-08 : 13:10:24

don't execute the string you are building in asp. just display it on the page. make sure you are building it correctly, with quotes in the right spot and all that. if you can't figure it out, then show us the string that is being built by the ASP page.

i.e., instead of

connection.execute SomeString

say

response.write SomeString

and look at the results on your page.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-08 : 13:24:30

It isn't the semi-colon at the end is it?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 13:25:57

Nope...cut and paste my code snippet into QA..

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources