Select date in british format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shannonwhitty@hotmail.com - 27 Apr 2008 05:35 GMT

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

Reply to this Message

David Portas - 27 Apr 2008 08:24 GMT

>I am able to extract dates in the correct format i.e.
>
[quoted text clipped - 19 lines]
> 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.

Signature

David Portas

Reply to this Message

shannonwhitty@hotmail.com - 30 Apr 2008 01:51 GMT

Figured it out.

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

> >I am able to extract dates in the correct format i.e.
> >
[quoted text clipped - 38 lines]
> --
> David Portas

Reply to this Message