EXTRACT in ORDER BY

[EarlStaley]

I have a table for our membership with a Date field for birthdays. I am trying to get a list of the birthdays ordered by month and date. My first attempt to get the list used this SQL:

"SELECT * FROM FSBC_Member WHERE BirthDate IS NOT NULL ORDER BY BirthDate,LastName,FirstName"

This resulted in a list ordered by oldest to youngest. I just want the birthdays without regard to the year of birth.

My next attempt to get the list used this SQL:

"SELECT * FROM FSBC_Member WHERE BirthDate IS NOT NULL ORDER BY EXTRACT(MONTH FROM BirthDate),EXTRACT(DAY FROM BirthDate), LastName, FirstName"

This did not work either. It appears I cannot use EXTRACT in an ORDER BY clause.

Is there a good way to order the query by month and day from a Date field short of redesigning the table to extract month and date from the Date field?

I am using the Borland Database Engine for Paradox 7 tables.

Thank You...
Earl Staley

 

[JarlH]

Seems like your dbms product doesn't support order by expression.

One possbile workaround is to create a view with two extra columns used by the order by.

Something like:[tt]
CREATE VIEW FSBC_Member_View AS
SELECT FSBC_Member.*,
EXTRACT(MONTH FROM BirthDate) as Bmonth,
EXTRACT(DAY FROM BirthDate) Bday
FROM FSBC_Member;

SELECT * FROM FSBC_Member_View
WHERE BirthDate IS NOT NULL
ORDER BY Bmonth, Bday, LastName, FirstName;
[/tt]

 

[EarlStaley]

JarlH,

I had already tried your suggestion and that didn't work either. The only place an EXTRACT statement works in my implementation is in a WHERE clause. For example:

"SELECT * FROM FSBC_Member WHERE EXTRACT(MONTH FROM BirthDate)=1 ORDER BY LastName, FirstName"

That is the only place I have found to successfully use the EXTRACT statement.

I have not found a source for the complete set of SQL statements that work with the Borland Database Engine using Paradox 7 tables and the Borland C++ 2010 compiler.

Where does the code to execute SQL statements reside?
Is the code in the Borland Database Engine?
Is the code in the Paradox 7 table structure?
Is the code in the Borland C++2010 compiler?

I am like a monkey using SQL. I don't really know how it works because I have no idea how it is implemented. I can look at a book and determine appropriate SQL statements to put in my C++ code but I would really like to know how and where it is implemented.

I have a very good book entitled "Beginning SQL" that guides me through SQL statements.

Thanks again for your help.

Earl