SQL query by Date- Problems (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

dupati1 -> SQL query by Date- Problems (10/8/2003 13:13:52)

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';

//vw_Absence_Report is a view formed by using other tables of the database

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



Long Island Lune -> RE: SQL query by Date- Problems (10/8/2003 15:07:42)

Did you try:

SELECT * FROM vw_Absence_Report WHERE StartDate > '2003/09/30'



BeTheBall -> RE: SQL query by Date- Problems (10/8/2003 21:40:27)

I am not at work to check this, but if I recall correctly, there should be no characters around your date. I think it is simply this:

SELECT * FROM vw_Absence_Report WHERE StartDate > 2003/09/30



Spooky -> RE: SQL query by Date- Problems (10/8/2003 22:22:13)

Have you tried writing the SQL string?

eg:

response.write SQL
Response.end



dupati1 -> RE: SQL query by Date- Problems (10/9/2003 9:24:14)

Thanks All,

Thanks for your suggestions. Finally i solved problem. As i said that vw_Absence_Report was a view that i was using in my SQL statement, i dont know why ASP SQL cannot reference to the views when trying for some variations..

I tried to run the query
SELECT * FROM vw_Absence_Report WHERE StartDate > '2003-09-30'
in the query analyzer and it worked fine.

so then i decided to create a new view which contained only the newer records new than 2003-09-30 and then i referenced this view on the ASP page as

SELECT * FROM vw_newAbsence_Report

Then it worked fine.

But still i am not sure why the previous sql statement did not work.

Thanks

Vijay



Long Island Lune -> RE: SQL query by Date- Problems (10/9/2003 12:48:59)

Hi Dupati1,

Did you mean in your original SQL?

quote:

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

The reason it didn't work was because you used "-"; ('2003-09-30') instead of "/"; '2003/09/30' in your date. If that's what you meant...

[;)]



dupati1 -> RE: SQL query by Date- Problems (10/9/2003 14:15:45)

Nope,

even '2003/09/30' did not work. It only worked when i created a new view and referenced it in the query.

Thanks

Vijay



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
6.347656E-02