On 5 Feb, 15:31, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> "davidjohnl...@googlemail.com" <davidjohnl...@googlemail.com> wrote:
>
> > I'm trying to convert the following query into mySQL, this currently
> > working with MS SQL although not with mySQL.
>
> > SET DATEFORMAT dmy declare @d datetime set @d = '01/12/2007' select *
> > from matrix.subscriptions where @d between startdate and enddate
>
> > Anyone got any idea how this can be done within mySQL
>
> SET @d = STR_TO_DATE('01/12/2007', '%m/%d/%Y');
> SELECT ... WHERE @d BETWEEN startdate AND enddate;
>
> RTFM on STR_TO_DATE() and friends:http://dev.mysql.com/doc/refman/5.0/...functions.html
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

Surely that'd be
SET @d = STR_TO_DATE('01/12/2007', '%d/%m/%Y');
SELECT ... WHERE @d BETWEEN startdate AND enddate;

Or more concisely:
SELECT ... WHERE STR_TO_DATE('01/12/2007', '%d/%m/%Y') BETWEEN
startdate AND enddate;