SEO

#1 (**permalink**) 02-28-2008, 09:06 AM

Hi,

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

The above query searches a date between two fields. (startdate and
enddate)

Anyone got any idea how this can be done within mySQL

Thanks for your help

David

#2 (**permalink**) 02-28-2008, 09:06 AM

On 5 Feb, 13:05, "davidjohnl...@googlemail.com"
<davidjohnl...@googlemail.com> wrote:
> Hi,
>
> 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
>
> The above query searches a date between two fields. (startdate and
> enddate)
>
> Anyone got any idea how this can be done within mySQL
>
> Thanks for your help
>
> David

Reformat the date to the ISO one used by MySQL.

#3 (**permalink**) 02-28-2008, 09:06 AM

"davidjohnlong@googlemail.com" <davidjohnlong@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/

#4 (**permalink**) 02-28-2008, 09:06 AM

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;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

mySQL date querying