×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

EXTRACT in ORDER BY

EXTRACT in ORDER BY

EXTRACT in ORDER BY

(OP)
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
 

RE: EXTRACT in ORDER BY

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

 

RE: EXTRACT in ORDER BY

(OP)
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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close