Visual Basic&database, SQL Order By

Sgit
#1 / 4

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

Michael V. Chernen
#2 / 4

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

Juan A. Barca Garcí
#3 / 4

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

Tom Rowlan
#4 / 4

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