how do i truncate the datetime returend so that it does not have the
time in it. i tried this based on docs that it will return the date
part but it does not work that way for me. it returns datetime.
select DATE(D.date_Time) from alerts d where d.alertHeadingRawdata
like '%bought%'

DATE(D.date_Time)
01/12/2003 12:00:00 AM

Junkone schreef:
> how do i truncate the datetime returend so that it does not have the
> time in it. i tried this based on docs that it will return the date
> part but it does not work that way for me. it returns datetime.
> select DATE(D.date_Time) from alerts d where d.alertHeadingRawdata
> like '%bought%'
>
> DATE(D.date_Time)
> 01/12/2003 12:00:00 AM

select DATE(datetimefield) from table;
returns only the date part.


mysql> show variables like 'date%';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)


--
Luuk

Luuk wrote:
> Junkone schreef:
>> how do i truncate the datetime returend so that it does not have the
>> time in it. i tried this based on docs that it will return the date
>> part but it does not work that way for me. it returns datetime.
>> select DATE(D.date_Time) from alerts d where d.alertHeadingRawdata
>> like '%bought%'
>>
>> DATE(D.date_Time)
>> 01/12/2003 12:00:00 AM
>
> select DATE(datetimefield) from table;
> returns only the date part.
>
>
> mysql> show variables like 'date%';
> +-----------------+-------------------+
> | Variable_name | Value |
> +-----------------+-------------------+
> | date_format | %Y-%m-%d |
> | datetime_format | %Y-%m-%d %H:%i:%s |
> +-----------------+-------------------+
> 2 rows in set (0.00 sec)
>
>

What version are you using? I am using 5.1.11-beta-log (for
OpenVMS/Alpha) (that is the ORIGINAL 64bit RISC processor from Digital
Equipment Corp - designed before they were HPaq'ed(compaq+HP))

mysql> show variables like 'date%';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.24 sec)

\
mysql> select a, date(a) as dt, cast( a as date) as justdate from a;
+---------------------+------------+------------+
| a | dt | justdate |
+---------------------+------------+------------+
| 2008-04-05 12:46:33 | 2008-04-05 | 2008-04-05 |
| 2008-04-05 12:46:36 | 2008-04-05 | 2008-04-05 |
| 2008-04-05 12:46:37 | 2008-04-05 | 2008-04-05 |
+---------------------+------------+------------+
3 rows in set (0.00 sec)