BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.

Hello,

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted. 23/05/2007 00:00:00

I tried the following

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:

Code Snippet

declare @.dt datetime

set @.dt = 20/5/2007

select @.dt

You could rewrite second query with explicit convert:

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)

Or use

Code Snippet

SET DATEFORMAT dmy

for setting default dateformat

|||That is because the implicit conversion thinks the date is a US date.

Do this:

.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)

or

SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||

steve_1_rm,

Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.

SELECT *

FROM Calls

WHERE

clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN '20070527' AND '20070530'

AMB

|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||

What do you get when you issue

Code Snippet

SELECT CONVERT(varchar(10), [Date], 103) as [Date], *

FROM Calls

WHERE [Date] BETWEEN '20070521' AND '20070531'