Re: Select date in british format

<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]
 @[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