"Claudia M. Castaneda" wrote:
>
> Hi,
> I have the following data:
>
> mysql> select obs_id, obs_URI, obs_source, beg_date from observation;
> +--------+------------------------------------+------------+------------+
> | obs_id | obs_URI | obs_source | beg_date |
> +--------+------------------------------------+------------+------------+
> | 4 | URIRAC://landsat-7/19990419/120850 | landsat-7 | 1999-04-19 |
> | 3 | URIRAC://landsat-7/19990519/90850 | landsat-7 | 1999-05-19 |
> | 2 | URIRAC://landsat-7/19990519/90850 | GOES-J | 1999-05-19 |
> | 1 | URIRAC://noaa-14/19990124/90850 | noaa-14 | 1999-01-24 |
> | 5 | URIRAC://landsat-7/19990419/120850 | landsat-7 | 1999-04-19 |
> | 6 | URIRAC://goes-j/19990202/111250 | goes-j | 1999-02-02 |
> +--------+------------------------------------+------------+------------+
> 6 rows in set (0.01 sec)
>
> I do
>
> mysql> select obs_id, obs_URI, obs_source, beg_date from observation where
> beg_date = '19990519';
> +--------+-----------------------------------+------------+------------+
> | obs_id | obs_URI | obs_source | beg_date |
> +--------+-----------------------------------+------------+------------+
> | 3 | URIRAC://landsat-7/19990519/90850 | landsat-7 | 1999-05-19 |
> | 2 | URIRAC://landsat-7/19990519/90850 | GOES-J | 1999-05-19 |
> +--------+-----------------------------------+------------+------------+
> 2 rows in set (0.01 sec)
>
> and
>
> mysql> select obs_id, obs_URI, obs_source, beg_date, beg_time from
> observation where beg_date between '1999-01-20' and '1999-05-30';
> +--------+------------------------------------+------------+------------+----------+
> | obs_id | obs_URI | obs_source | beg_date |
> beg_time |
> +--------+------------------------------------+------------+------------+----------+
> | 4 | URIRAC://landsat-7/19990419/120850 | landsat-7 | 1999-04-19 |
> 12:08:50 |
> | 3 | URIRAC://landsat-7/19990519/90850 | landsat-7 | 1999-05-19 |
> 09:08:50 |
> | 2 | URIRAC://landsat-7/19990519/90850 | GOES-J | 1999-05-19 |
> 09:08:50 |
> | 1 | URIRAC://noaa-14/19990124/90850 | noaa-14 | 1999-01-24 |
> 09:08:50 |
> | 5 | URIRAC://landsat-7/19990419/120850 | landsat-7 | 1999-04-19 |
> 12:08:50 |
> | 6 | URIRAC://goes-j/19990202/111250 | goes-j | 1999-02-02 |
> 11:12:50 |
> +--------+------------------------------------+------------+------------+----------+
> 6 rows in set (0.01 sec)
>
> but the following sql command does not seem to work as I expected it to:
>
> mysql> select obs_id, obs_URI, obs_source, beg_date, beg_time from
> observation where beg_date between '19990124' and '19990519';
> Empty set (0.01 sec)
>
> or
>
> mysql> select obs_id, obs_URI, obs_source, beg_date, beg_time from
> observation where beg_date between '19990120' and '19990530';
> Empty set (0.01 sec)
>
> Could any one explain this to me, please?
>
> ---
> Claudia M. Castaneda
Hi Claudia
Your problem comes from the single quotes arount the date.
Because of these, there is no automatic convertion into the date string format.
Try:
SELECT
obs_id
, obs_URI
, obs_source
, beg_date
, beg_time
FROM
observation
WHERE
beg_date BETWEEN 19990120 AND 19990530;
Tschau
Christian
