SEO

I am able to extract dates in the correct format i.e.

SELECT CONVERT(VARCHAR(8), GETDATE(), 3)
=> dd/mm/yy

My issue is that my users are selecting a date in this format and I
need to select data based on this range.

i.e.

SELECT * FROM TABLE
WHERE date BETWEEN '01/11/2007' AND '30/11/2007'
=> The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.
(Obviously expecting to see mm/dd/yyyy)

Next Try:
SELECT * FROM TABLE
WHERE CONVERT(VARCHAR(8), date, 3) BETWEEN '01/11/07' AND '30/11/07'
=> Returns rows outside of required range

What do I need to do to select all data in my table where the data
range is between 01/11/07 AND 30/11/07 in this format dd/mm/yy ???

<shannonwhitty@hotmail.com> wrote in message
news:ac0e8dad-edf6-4d28-99d5-9bd86950a050@r9g2000prd.googlegroups.com...
>I am able to extract dates in the correct format i.e.
>
> SELECT CONVERT(VARCHAR(8), GETDATE(), 3)
> => dd/mm/yy
>
> My issue is that my users are selecting a date in this format and I
> need to select data based on this range.
>
> i.e.
>
> SELECT * FROM TABLE
> WHERE date BETWEEN '01/11/2007' AND '30/11/2007'
> => The conversion of a char data type to a datetime data type resulted
> in an out-of-range datetime value.
> (Obviously expecting to see mm/dd/yyyy)
>
> Next Try:
> SELECT * FROM TABLE
> WHERE CONVERT(VARCHAR(8), date, 3) BETWEEN '01/11/07' AND '30/11/07'
> => Returns rows outside of required range
>
> What do I need to do to select all data in my table where the data
> range is between 01/11/07 AND 30/11/07 in this format dd/mm/yy ???


I assume these are DATETIMEs? DATETIMEs don't have any format in SQL Server.

After validating the users input, your client application should query the
database using DATETIME or SMALLDATETIME types (which don't have any
format).

Your queries therefore ought to look something like:

BETWEEN @dt1 AND @dt2

where @dt1 and @dt2 are DATETIME or SMALLDATETIME types.

In fact it is better to use >= and < rather than BETWEEN. DATETIME values
always contain both date and time elements. If you use BETWEEN then any
times after midnight on the last day of the period will be excluded.

--
David Portas

Figured it out.

SELECT * FROM TABLE
WHERE date BETWEEN CONVERT(DATETIME, '01/11/2007' ,3) AND
CONVERT(DATETIME, '30/11/2007' ,3)


David Portas wrote:

> <shannonwhitty@hotmail.com> wrote in message
> news:ac0e8dad-edf6-4d28-99d5-9bd86950a050@r9g2000prd.googlegroups.com...
> >I am able to extract dates in the correct format i.e.
> >
> > SELECT CONVERT(VARCHAR(8), GETDATE(), 3)
> > => dd/mm/yy
> >
> > My issue is that my users are selecting a date in this format and I
> > need to select data based on this range.
> >
> > i.e.
> >
> > SELECT * FROM TABLE
> > WHERE date BETWEEN '01/11/2007' AND '30/11/2007'
> > => The conversion of a char data type to a datetime data type resulted
> > in an out-of-range datetime value.
> > (Obviously expecting to see mm/dd/yyyy)
> >
> > Next Try:
> > SELECT * FROM TABLE
> > WHERE CONVERT(VARCHAR(8), date, 3) BETWEEN '01/11/07' AND '30/11/07'
> > => Returns rows outside of required range
> >
> > What do I need to do to select all data in my table where the data
> > range is between 01/11/07 AND 30/11/07 in this format dd/mm/yy ???
>
>
> I assume these are DATETIMEs? DATETIMEs don't have any format in SQL Server.
>
> After validating the users input, your client application should query the
> database using DATETIME or SMALLDATETIME types (which don't have any
> format).
>
> Your queries therefore ought to look something like:
>
> BETWEEN @dt1 AND @dt2
>
> where @dt1 and @dt2 are DATETIME or SMALLDATETIME types.
>
> In fact it is better to use >= and < rather than BETWEEN. DATETIME values
> always contain both date and time elements. If you use BETWEEN then any
> times after midnight on the last day of the period will be excluded.
>
> --
> David Portas