471,119 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,119 software developers and data experts.

SQL Between Dates

@sh
For some reason guys my SQL string isn't working and I'm probably doing
something stupid?

SELECT *
FROM sol_session
SELECT *
FROM SOL_Session
WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

The 'DateGiven' field is a DateTime field, and I can't see quite whats
wrong??

I simply get a Syntax error!

Cheers, Ash
Sep 13 '06 #1
15 29320
On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:
WHERE DateGiven '10/09/2006' AND <
'13/09/2006'
WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT
Sep 13 '06 #2
@sh
Ahhhhh sorry for being silly, knew it'd be something obvious, not having a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com...
On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:
> WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT

Sep 13 '06 #3
@sh
An update actually, based on the SQL you suggested I am getting all records
returned, with no filter - including dates from 2003, 2004 etc?

SELECT *
FROM sol_session
SELECT *
FROM SOL_Session
WHERE DateGiven >= '10/09/06' AND DateGiven
<= '13/09/06'

Any ideas?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message news:O9********************@bt.com...
Ahhhhh sorry for being silly, knew it'd be something obvious, not having a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com...
>On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:
>> WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT


Sep 13 '06 #4
@sh
An update actually, based on the SQL you suggested I am getting all records
returned, with no filter - including dates from 2003, 2004 etc?

SELECT *
FROM sol_session
SELECT *
FROM SOL_Session
WHERE DateGiven >= '10/09/06' AND DateGiven
<= '13/09/06'

Any ideas?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message news:O9********************@bt.com...
Ahhhhh sorry for being silly, knew it'd be something obvious, not having a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com...
>On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:
>> WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT



Sep 13 '06 #5
BETWEEN is inclusive of the values being tested, the equivelent of
changing to >= and < to <=. BETWEEN also has significant problems
when the datetime column has a non-zero time, because the end date has
not time and any rows with non-zero times for that date are outside
the range, and thus excluded. You are better off avoiding BETWEEN for
dates.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 12:24:20 +0100, "@sh" <sp**@spam.comwrote:
>Ahhhhh sorry for being silly, knew it'd be something obvious, not having a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com.. .
>On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:
>> WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT
Sep 13 '06 #6
Is DateGiven a datetime datatype? It sounds like it is a string, and
of course string comparison is from left to right, character by
character.

If you must store dates as strings - datetime is much better - the
safest way is yyyymmdd format. That is also the preferred format for
giving any date string to SQL Server as it avoids all the confusion of
October 9 vs September 10 that occurs when working internationally.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 12:30:26 +0100, "@sh" <sp**@spam.comwrote:
>An update actually, based on the SQL you suggested I am getting all records
returned, with no filter - including dates from 2003, 2004 etc?

SELECT *
FROM sol_session
SELECT *
FROM SOL_Session
WHERE DateGiven >= '10/09/06' AND DateGiven
<= '13/09/06'

Any ideas?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message news:O9********************@bt.com...
>Ahhhhh sorry for being silly, knew it'd be something obvious, not having a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com.. .
>>On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:

WHERE DateGiven '10/09/2006' AND <
'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT

Sep 13 '06 #7
@sh
Thanks for your reply Roy, yes DateGiven is a datetime field type, hence the
reason I'm confused as to this not working? Is there any other reason that
I'd get 2003/2004/2005 dates listed? Is there a way that I can force SQL to
read the DateGiven field as a Date subtype? And perhaps force the input
dates to datetime format too?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:2f********************************@4ax.com...
Is DateGiven a datetime datatype? It sounds like it is a string, and
of course string comparison is from left to right, character by
character.

If you must store dates as strings - datetime is much better - the
safest way is yyyymmdd format. That is also the preferred format for
giving any date string to SQL Server as it avoids all the confusion of
October 9 vs September 10 that occurs when working internationally.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 12:30:26 +0100, "@sh" <sp**@spam.comwrote:
>>An update actually, based on the SQL you suggested I am getting all
records
returned, with no filter - including dates from 2003, 2004 etc?

SELECT *
FROM sol_session
SELECT *
FROM SOL_Session
WHERE DateGiven >= '10/09/06' AND DateGiven
<= '13/09/06'

Any ideas?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message
news:O9********************@bt.com...
>>Ahhhhh sorry for being silly, knew it'd be something obvious, not having
a
good week!!!

Is it possible to use BETWEEN for such a SQL statement?

Cheers, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com. ..
On Wed, 13 Sep 2006 11:59:46 +0100, "@sh" <sp**@spam.comwrote:

WHERE DateGiven '10/09/2006' AND <
>'13/09/2006'

WHERE DateGiven '10/09/2006'
AND DateGiven < '13/09/2006'

You need to specify DateGiven for each comparison.

Roy Harvey
Beacon Falls, CT


Sep 13 '06 #8
On Wed, 13 Sep 2006 13:14:29 +0100, "@sh" <sp**@spam.comwrote:
>Thanks for your reply Roy, yes DateGiven is a datetime field type, hence the
reason I'm confused as to this not working? Is there any other reason that
I'd get 2003/2004/2005 dates listed? Is there a way that I can force SQL to
read the DateGiven field as a Date subtype? And perhaps force the input
dates to datetime format too?

Cheers, @sh
There is no Date type or subtype in SQL Server. (Don't get me
started!)

What do you get when you test these variations?

SELECT *
FROM SOL_Session
WHERE DateGiven >= '10 Sep 2006'
AND DateGiven <= '13 Sep 2006'

SELECT *
FROM SOL_Session
WHERE DateGiven >= '20060910'
AND DateGiven <= '20060913'

Roy Harvey
Beacon Falls, CT
Sep 13 '06 #9
@sh
Thanks again for your help here!!
SELECT *
FROM SOL_Session
WHERE DateGiven >= '10 Sep 2006'
AND DateGiven <= '13 Sep 2006'
I get a listing of CORRECT results ;o)

SELECT *
FROM SOL_Session
WHERE DateGiven >= '20060910'
AND DateGiven <= '20060913'
Another list of correct results - are these good or bad findings?

Cheers, @sh
Sep 13 '06 #10
@sh
Interestingly, the following SQL works perfectly as well...

SELECT *
FROM sol_session sol_session_1
WHERE (DateGiven = '13/09/2006')

But change the = to a and it doesn't give me any results!?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message news:us********************@bt.com...
Thanks again for your help here!!
>SELECT *
FROM SOL_Session
WHERE DateGiven >= '10 Sep 2006'
AND DateGiven <= '13 Sep 2006'

I get a listing of CORRECT results ;o)

>SELECT *
FROM SOL_Session
WHERE DateGiven >= '20060910'
AND DateGiven <= '20060913'

Another list of correct results - are these good or bad findings?

Cheers, @sh

Sep 13 '06 #11
This is good news. It means the original versions were string
comparisons, not date comparisons. For whatever reason, SQL Server
apparently decided that the string were not dates, so it converted in
the other direction (datetime to string) and thus the strange results.
So all you have to do is use string formats that SQL Server recognizes
(as in the tests), or explicit conversions:

SELECT *
FROM SOL_Session
WHERE DateGiven >= CONVERT(datetime,'10/09/06',3)
AND DateGiven <= CONVERT(datetime,''13/09/06'',3)

Note that the third parameter indicates the specific format of the
string as dd/mm/yy. See the documentation on CONVERT for
alternatives.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 13:29:44 +0100, "@sh" <sp**@spam.comwrote:
>Thanks again for your help here!!
>SELECT *
FROM SOL_Session
WHERE DateGiven >= '10 Sep 2006'
AND DateGiven <= '13 Sep 2006'

I get a listing of CORRECT results ;o)

>SELECT *
FROM SOL_Session
WHERE DateGiven >= '20060910'
AND DateGiven <= '20060913'

Another list of correct results - are these good or bad findings?

Cheers, @sh
Sep 13 '06 #12
@sh
Hang on......this query now works fine...what has changed or am I being
silly?

SELECT *
FROM sol_session
WHERE (DateGiven >= '10/09/2006') AND (DateGiven <= '12/09/2006')
ORDER BY dategiven DESC

Is it the brackets?

Cheers, @sh

"@sh" <sp**@spam.comwrote in message news:bd********************@bt.com...
Interestingly, the following SQL works perfectly as well...

SELECT *
FROM sol_session sol_session_1
WHERE (DateGiven = '13/09/2006')

But change the = to a and it doesn't give me any results!?

Cheers, @sh
"@sh" <sp**@spam.comwrote in message
news:us********************@bt.com...
>Thanks again for your help here!!
>>SELECT *
FROM SOL_Session
WHERE DateGiven >= '10 Sep 2006'
AND DateGiven <= '13 Sep 2006'

I get a listing of CORRECT results ;o)

>>SELECT *
FROM SOL_Session
WHERE DateGiven >= '20060910'
AND DateGiven <= '20060913'

Another list of correct results - are these good or bad findings?

Cheers, @sh


Sep 13 '06 #13
I don't know. An example that fails tells us a lot more than one that
works. The question is, can you reproduce the failures?

One thought came to mind just now. A mistake in typing in the date
string resulted in a string that could not be converted to a datetime
would cause the sort of problem under discussion.

Roy

On Wed, 13 Sep 2006 13:42:17 +0100, "@sh" <sp**@spam.comwrote:
>Hang on......this query now works fine...what has changed or am I being
silly?

SELECT *
FROM sol_session
WHERE (DateGiven >= '10/09/2006') AND (DateGiven <= '12/09/2006')
ORDER BY dategiven DESC

Is it the brackets?

Cheers, @sh
Sep 13 '06 #14
@sh
Apologies for your time, I no longer seem able to reproduce the error so
perhaps your suggestion that I had made a mistake in the date format perhaps
is true - but I do appreciate your help!

Have a good week!

Thanks, @sh
"Roy Harvey" <ro********@snet.netwrote in message
news:ei********************************@4ax.com...
>I don't know. An example that fails tells us a lot more than one that
works. The question is, can you reproduce the failures?

One thought came to mind just now. A mistake in typing in the date
string resulted in a string that could not be converted to a datetime
would cause the sort of problem under discussion.

Roy

On Wed, 13 Sep 2006 13:42:17 +0100, "@sh" <sp**@spam.comwrote:
>>Hang on......this query now works fine...what has changed or am I being
silly?

SELECT *
FROM sol_session
WHERE (DateGiven >= '10/09/2006') AND (DateGiven <= '12/09/2006')
ORDER BY dategiven DESC

Is it the brackets?

Cheers, @sh

Sep 13 '06 #15
@sh (sp**@spam.com) writes:
Apologies for your time, I no longer seem able to reproduce the error so
perhaps your suggestion that I had made a mistake in the date format
perhaps is true - but I do appreciate your help!
In your eariler posts, you had two queries. One was a plain SELECT *
without a WHERE clause...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 13 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Riley | last post: by
7 posts views Thread by Alistair | last post: by
5 posts views Thread by PW | last post: by
10 posts views Thread by Colin Steadman | last post: by
1 post views Thread by Don Sealer | last post: by
2 posts views Thread by Rachel Suddeth | last post: by
1 post views Thread by pitfour.ferguson | last post: by
7 posts views Thread by evilcowstare via AccessMonster.com | last post: by
2 posts views Thread by Jim Carlock | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.