Search through dates using like

[Ripper]

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there a
way to search for dates that occured in a particular month regardless of day
or year?

 

[Douglas J. Steele]

WHERE Month(MyDateField) = 9

 

[John W. Vinson]

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there a
way to search for dates that occured in a particular month regardless of day
or year?

Dates are not stored as strings, no matter what the format. You can use the
builtin functions Month([datefield]) (or Year([datefield]), Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of the
date.

 

[MGFoster]

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there a
way to search for dates that occured in a particular month regardless of day
or year?

Dates are not stored as strings, no matter what the format. You can use the
builtin functions Month([datefield]) (or Year([datefield]), Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of the
date.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For faster queries you can use the BETWEEN predicate:

PARAMETERS StartDate Date, EndDate Date;
SELECT ...
FROM...
WHERE date_column BETWEEN StartDate And EndDate

Using VBA functions like Month(), Year() are slower 'cuz the query
engine has to read every row in the table to evaluate the VBA function.

Be sure to index the date_column for even faster searches.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWZxHoechKqOuFEgEQJQvACbBt8Tev/q/VRhMja3lgn4yrfgXh4AoOVm
OkEMWh9LdT3wkY6sW1t0tACD
=n9zF
-----END PGP SIGNATURE-----

 

[Douglas J. Steele]

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there
a way to search for dates that occured in a particular month regardless
of day or year?

Dates are not stored as strings, no matter what the format. You can use
the
builtin functions Month([datefield]) (or Year([datefield]),
Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of
the
date.

For faster queries you can use the BETWEEN predicate:

PARAMETERS StartDate Date, EndDate Date;
SELECT ...
FROM...
WHERE date_column BETWEEN StartDate And EndDate

Using VBA functions like Month(), Year() are slower 'cuz the query
engine has to read every row in the table to evaluate the VBA function.

Be sure to index the date_column for even faster searches.

Not sure that would hold try to search for "all dates that happened in
September, regardless of year."

You'd need

SELECT ...
FROM...
WHERE date_column BETWEEN #1999-09-01# And #1999-09-30#
OR date_column BETWEEN #2000-09-01# And #2000-09-30#
OR date_column BETWEEN #2001-09-01# And #2001-09-30#
OR date_column BETWEEN #2002-09-01# And #2002-09-30#
etc.

 

[MGFoster]

On Wed, 7 Jan 2009 13:52:08 -0800, Ripper
<[email protected]>
wrote:

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there
a way to search for dates that occured in a particular month regardless
of day or year?
Dates are not stored as strings, no matter what the format. You can use
the
builtin functions Month([datefield]) (or Year([datefield]),
Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of
the
date.

For faster queries you can use the BETWEEN predicate:

PARAMETERS StartDate Date, EndDate Date;
SELECT ...
FROM...
WHERE date_column BETWEEN StartDate And EndDate

Using VBA functions like Month(), Year() are slower 'cuz the query
engine has to read every row in the table to evaluate the VBA function.

Be sure to index the date_column for even faster searches.

Not sure that would hold try to search for "all dates that happened in
September, regardless of year."

You'd need

SELECT ...
FROM...
WHERE date_column BETWEEN #1999-09-01# And #1999-09-30#
OR date_column BETWEEN #2000-09-01# And #2000-09-30#
OR date_column BETWEEN #2001-09-01# And #2001-09-30#
OR date_column BETWEEN #2002-09-01# And #2002-09-30#
etc.

Now ur getting esoteric ;-)