sql date searching syntax with joins

Hi,
this is probably dead simple, but i need a hand. I
have 2 tables, one called clients, the other called
invoices. The client table has a clientid, as does the
invoices table. The invoices table also has a field
called due_date, and invoice number. I want to write
an sql that will bring me back, the names of the
clients and the invoice numbers and the due_date
between certain dates, but i always get zero, here's
my sql

select clients.companyname , clients.fname,
clients.lname from clients left join invoices on
clients.clientid = invoices.cliendif where
invoices.due_date > curdate() and invoices.dau_date <
'31/12/2005';


is my join wrong?

many thanks



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: sql date searching syntax with joins

On 02/06/2005, Mark Mchugh wrote:

> select clients.companyname , clients.fname, clients.lname
> from clients
> left join invoices on clients.clientid = invoices.cliendif
> where
> invoices.due_date > curdate() and invoices.dau_date < '31/12/2005';

Don't type in queries; use cut-and-paste instead. Your query contains
typing errors.

You have to specify your end date in the following format: 'yyyy-mm-dd'
(i.e. '2005-12-31').

--
felix

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: sql date searching syntax with joins

Mark, have you tried specifying the date in another format? I use this
format: '2005.12.31'. Hope that helps...

-----Mensaje original-----
De: Mark Mchugh [mailto:mark_mch@yahoo.com]
Enviado el: Jueves, 02 de Junio de 2005 06:25 a.m.
Para: mysql list
Asunto: sql date searching syntax with joins

Hi,
this is probably dead simple, but i need a hand. I
have 2 tables, one called clients, the other called
invoices. The client table has a clientid, as does the
invoices table. The invoices table also has a field
called due_date, and invoice number. I want to write
an sql that will bring me back, the names of the
clients and the invoice numbers and the due_date
between certain dates, but i always get zero, here's
my sql

select clients.companyname , clients.fname,
clients.lname from clients left join invoices on
clients.clientid = invoices.cliendif where
invoices.due_date > curdate() and invoices.dau_date <
'31/12/2005';


is my join wrong?

many thanks



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=urcid@vw.com.mx

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: sql date searching syntax with joins

Use 'YYYY-MM-DD' date format. And you can take a look to BETWEEN() syntax in
MySQL manual ;-)


Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



----- Original Message -----
From: "Mark Mchugh"
To: "mysql list"
Sent: Thursday, June 02, 2005 1:25 PM
Subject: sql date searching syntax with joins


> Hi,
> this is probably dead simple, but i need a hand. I
> have 2 tables, one called clients, the other called
> invoices. The client table has a clientid, as does the
> invoices table. The invoices table also has a field
> called due_date, and invoice number. I want to write
> an sql that will bring me back, the names of the
> clients and the invoice numbers and the due_date
> between certain dates, but i always get zero, here's
> my sql
>
> select clients.companyname , clients.fname,
> clients.lname from clients left join invoices on
> clients.clientid = invoices.cliendif where
> invoices.due_date > curdate() and invoices.dau_date <
> '31/12/2005';
>
>
> is my join wrong?
>
> many thanks
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=petr@practisoft.cz
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org