From: John Daisley (john.daisleymypostoffice.co.uk)
Date: Wed Dec 31 2008 - 19:55:10 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Just thought I would take this opportunity to wish everyone on the list
a happy, prosperous and peaceful 2009.

John Daisley
Email: john.daisleybutterflysystems.co.uk
Mobile: 07812 451238

MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician

On Wed, 2008-12-31 at 10:23 -0800, Gary W. Smith wrote:

> Truncate the time part of the datetime field when doing the compare
>
> AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30'
>
> Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process.
>
>
> ________________________________
>
> From: Johnny Withers [mailto:johnnypixelated.net]
> Sent: Wed 12/31/2008 10:13 AM
> To: MySQL General List
> Subject: Compare DATETIME to DATE
>
>
>
> Hi,
> I don't quite understand (or even know) what the "proper" way to compare a
> DATETIME column to a given DATE value is. I've used various methods but I'd
> like to know if there's a better way to compare these values.
>
> Right now I have a query with this in the WHERE clause (customer.created_dt
> is a DATETIME):
>
> AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30'
>
> This was working (MySQL on Win32) before I moved the database to MySQL on
> RHEL 64-bit (5.0.45-log).
>
> Should that work?
>
> I've also done this:
>
> AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30
> 23:59:59'
>
> That works on both servers, but I really don't want to have to put the time
> in there (unless that's the way you are supposed to do this).
>
> I've though about using DATE_FORMAT... not sure about that either.
>
>
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnnypixelated.net
>
>
>
>
> ______________________________________________
> This email has been scanned by Netintelligence
> http://www.netintelligence.com/email

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]