SQL Order By 
Author Message
 SQL Order By

How do I structure an SQL statement to order a Dynaset by the Month and
Day of a date field?



Fri, 19 Mar 1999 03:00:00 GMT  
 SQL Order By


mellifluous words, full of wisdom and thoughtful reflections:

Quote:
>!<How do I structure an SQL statement to order a Dynaset by the Month and
>!<Day of a date field?

SQL Server:

SELECT XMonth=DATEPART(m,DateField), XDay=DATEPART(d,DateField), DateField FROM
        sometable WHERE something LIKE anything ORDER BY XMonth, XDay

The same will work with Access, if you access Access (no pun intended) via the
ODBC. Otherwise, use standard Access Date/Time functions to format the necessary
data (similar to Month(Now), Year(Now) - in VB).



Sat, 20 Mar 1999 03:00:00 GMT  
 SQL Order By



Quote:
> How do I structure an SQL statement to order a Dynaset by the Month and
> Day of a date field?

        I've not ever tried to order a Dynaset by Month and Day, but when I have
to select records by month and day, I use a code like this:

        "SELECT * FROM TABLE WHERE Month(Date_Field) = " & _
        Month(Date_Value) & " AND Day(Date_Field) = " & _
        Day(Date_Value),
        so you can try to use something like

        ORDER BY Month(Date_Field), Day(Date_Field)

        This can depend on the type of SQL interpreter you're using. I know it
works with Access.



Tue, 23 Mar 1999 03:00:00 GMT  
 SQL Order By


Quote:
> =


> > How do I structure an SQL statement to order a Dynaset by the Month a=
nd
> > Day of a date field?

> =
>         I've not ever tried to order a Dynaset by Month and Day, but wh=
en I have
> to select records by month and day, I use a code like this:
> =
>         "SELECT * FROM TABLE WHERE Month(Date_Field) =3D " & _
>         Month(Date_Value) & " AND Day(Date_Field) =3D " & _
>         Day(Date_Value),
>         so you can try to use something like
> =
>         ORDER BY Month(Date_Field), Day(Date_Field)
> =
>         This can depend on the type of SQL interpreter you're using. I =
know it
> works with Access.

Some SQL Interpreters (IBM DB2/2 comes to mind...) require that any
field (or calculated value) MUST be SELECTed to be used in the ORDER BY
clause. Therefore, your SQL would look something like:

        SELECT [field1], [field2], Month(Date_Field), Day(Date_Field) FROM
[Table]
        Where [Where conditions]
        ORDER BY Month(Date_Field), Day(Date_Field)

Check the documentation for your database engine for more specific
details.
Also, be VERY careful about using "SELECT * ..." in production-level
code. Any changes to the underlying database tables can cause errors in
your application. It is almost always better to explicitly list those
fields that you wish to select.

HTH...

Tom



Fri, 26 Mar 1999 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. SQL: Order by Multiple Fields?

2. SQL Syntax error in ORDER BY clause.(-2147217900)

3. How to ORDER BY in SQL Statement

4. sql search and order by

5. SQL and sorting order

6. SQL Problem: Cannot Use Distinct when using the Month function in Order By

7. SQL: ORDER BY detonates VB32.exe

8. Fields order in sql query

9. listbox ordering after an SQL query

10. SQL execution order?

11. SQL ORDER BY

12. SQL...Order by random

 

 
Powered by phpBB® Forum Software