|
| |
|
|
dupati1
Posts: 84
Joined: 10/6/2003
Status: offline
 |
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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
Posts: 6487
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
| |
|
|
|
dupati1
Posts: 84
Joined: 10/6/2003
Status: offline
|
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
| |
|
|
|
dupati1
Posts: 84
Joined: 10/6/2003
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
ky 