[RESOLVED] BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

[steve_rm]

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.

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

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

[Ecniv]

Sql server uses american dates, ie mm/dd/yyyy.

Szlamany can provide further info - or there is the faq at the top or search online/help files.

You may prefer to use dd-mmm-yyyy format as at least you definately know the month is right.


Also, in oracle it requests that dates provided are dates and not strings, so you may need to convert to a date (not sure of the sql server function, but oracle is to_date('string','format'))

[GaryMazzone]

The dates need to be converted to US standard date format (mm/dd/yyyy) when performing SQL seraches with them

So I think the query would look like this:

Select * From Calls Where ClienName In ('Bankside Engineering Limited') 
AND scheduleDate BETWEEN '5/20/2007' AND '5/30/2007'

I'm assuming that 20/5 is May 20th and 30/5 is May 30th

[kaffenils]

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

Try to avoid using the date format you have used. I have explained why this is bad in this thread http://vbforums.com/showpost.php?p=2895295&postcount=16

Also, there is no need to use IN when you only have one value. Use = instead.

[steve_rm]

Hello,

I am still having a problem, i tried the following

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') 
AND [Date] BETWEEN '20070521' AND '20070531'

I have this date stored in the database, but does not return and it should.
23/05/2007 00:00:00

using the format yyyymmdd


Thanks,

[kaffenils]

Wrap the dates in single quotes like this '20070530'

[steve_rm]

Hello,

Thanks for your continued help.

I have done this now. but still no rows returned.

SELECT * FROM Calls 
WHERE [Date] BETWEEN '20070521' AND '20070531'

This date is stored like this in the database, it should be returned.
23/05/2007 00:00:00

[GaryMazzone]

What is the format of the date stored in the database? (dd/mm/yyyy or yyyymmdd). If you want to use the yyyymmdd and the stored data is dd/mm/yyyy then you need to format the database data in the in Where condition to the same. Or use US date format.

[kaffenils]

What is the format of the date stored in the database? (dd/mm/yyyy or yyyymmdd). If you want to use the yyyymmdd and the stored data is dd/mm/yyyy then you need to format the database data in the in Where condition to the same. Or use US date format.

Sorry, that is wrong. A date is always stored at two 4 byte integers. The first bytes representing number of days after 01-01-1900 and the second four bytes as number of 1/300 seconds after midnight.

When you SELECT dates from the table they are formatted based on the regional settings on your pc.

When you hardcode datetime columns in a WHERE clause, as done by steve_rm, SQL Server will try to convert the text string into a valid date format. The problem is that there are many different formats using / as the date separator. If you write 05/06/07, which for you is May 6 2007 (US format), for me that is June 5 2007. There are two more formats using /.
The safest thing is therefore to write the date string without any separators. Why? Because the only format not using any separators is the ISO format, and it is ALWAYS yyyymmdd.

[GaryMazzone]

It might be the safest to save as yyyymmdd but if he already has the dates as dd/mm/yyyy then he needs to do something else just passing in yyyymmdd will not help him as he has already stated. Just try what I suggested and it will probably work. The ANSI standard for SQL uses US date format for dates.

[kaffenils]

It might be the safest to save as yyyymmdd but if he already has the dates as dd/mm/yyyy then he needs to do something else just passing in yyyymmdd will not help him as he has already stated.

As I said, using yyyymmdd will always work. Trust me

steve_rm, can you paste rthe results of the following select here:

select top 20 from convert(varchar(8), [Date],112) Calls where clientname='Bankside Engineering Limited'

[steve_rm]

Hello,

Still having a problem, sorry. The code I am using is this.

SELECT * FROM Calls 
WHERE [Date] BETWEEN '20070521' AND '20070531'

In my database i have a date like this: 23/05/2007 00:00:00

Now my query should return this row, but it doesn't.

I changed my regional setting on my computer to US standard. That didn't make any difference.

Any other ideas?

[GaryMazzone]

Did you try the query I suggested in Post#3?

[brucevde]

How are you executing this query? If you are using VB, ADO and populating a recordset, how do you check if there are no records? You may want to post that code.

[steve_rm]

Thanks guys, finally got this sorted.

It was a mixture of the above examples.

Thanks