Figured it out.
SELECT * FROM TABLE
WHERE date BETWEEN CONVERT(DATETIME, '01/11/2007' ,3) AND
CONVERT(DATETIME, '30/11/2007' ,3)
David ****tas wrote:
> <shannonwhitty@[EMAIL PROTECTED]
> wrote in message
> news:ac0e8dad-edf6-4d28-99d5-9bd86950a050@[EMAIL PROTECTED]
> >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 @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
>
> where @[EMAIL PROTECTED]
and @[EMAIL PROTECTED]
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 ****tas
|
