By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,737 Members | 1,478 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,737 IT Pros & Developers. It's quick & easy.

Select date in british format

P: n/a
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 ???
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<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



Jun 27 '08 #2

P: n/a
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
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.