Select date in british format

[shannonwhitty]

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 ???

[David Portas]

<sh***********@hotmail.comwrote in message
news:ac**********************************@r9g2000p rd.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

[shannonwhitty]

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:

<sh***********@hotmail.comwrote in message
news:ac**********************************@r9g2000p rd.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