date in SQL

[weee]

I want to show only the records in the databse when the the date of their service didn't expire yet.
how the SQL should look like?

SQL = "SELECT id,companyName,serviceStartDate FROM compTBL WHERE ORDER BY companyName"

Thanks!

[buntine]

This SQL query will cause ann error. You cannot mix the WHERE and ORDER BY clause's. WHERE is used for conditional statements, whilst ORDER BY is used to determine the order of the extracted records.

SQL has built-in date handling functions. Some of which, can be seen at the following web page.

Your SQL query should look something like this.

SQL = "SELECT id, companyName, serviceStartDate FROM compTBL WHERE DATEDIFF(DAY, serviceStartDate, GETDATE()) <= 0"

The preceding query uses the built-in DateDiff() function, which returns the number of intervals in between two dates according to the abbreviation given as the first argument. In this case, it was DAY.

GETDATE() will return the current system date and time.

Regards,
Andrew Buntine.

[weee]

This is wht I got:

SQL = "SELECT id,serviceStartDate,companyName FROM compTBL WHERE DATEDIFF(DAY, serviceStartDate, GETDATE()) <= 0"

I'm getting this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GETDATE' in expression.

I'm working with Access.
What is the problem?

Thanks

[NCit]

weee

Try to replace GETDATE() with NOW(). GETDATE() is valid in SQLServer while NOW() is valid in MS Access (which you use). Also your date field in your database MUST BE in date/text format. If you store your dates in a text format (like 20040601142500, like me!!), this SQL Query won't work.

If you write your database structure, maybe we may help more effectively.

[weee]

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

This is my SQL:
SQL = "SELECT id,serviceStartDate,companyName FROM compTBL WHERE DATEDIFF(DAY, serviceStartDate, NOW()) <= 0"

Why's that?

My date field is in date/text format.

[NCit]

Try changing SQL Query with the one below;


SQL = "SELECT id, companyName, serviceStartDate FROM compTBL WHERE DateDiff("d",Now(),serviceStartDate) <= 0"

[weee]

there's was a problem with the quates of the "d" so I made it ""d"" and I got the same error as before.
SQL = "SELECT id, serviceStartDate, serviceStartDate FROM compTBL WHERE DateDiff(""d"",Now(),serviceStartDate) <= 0"

What can it be?