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
15  29421 
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
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
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
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
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
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
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
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
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
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
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
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
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
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
@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Riley |
last post by:
The date fields being saved by a VB program were being saved
as #2003-11-22#. For reasons unknown to me these dates began
to be saved as "11/22/2003"
All of these dates were made dates with the...
|
by: Alistair |
last post by:
diary_date = request.form("diary_date") - (from a populated drop down
list)
strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"
a response.write...
|
by: PW |
last post by:
<rant>
Sorry guys, but I just have to whinge. Dates in ASP are a total pain in the
butt! I seem to get caught out so many times. I realise its my own fault,
but going from the posts in this...
|
by: Colin Steadman |
last post by:
I'm a stupid ASP programmer and I dont do Javascript (except for very
simple tasks anyway), and I'm in a bit of a predicament. I've used a
javascript table sorting script from here:
...
|
by: Don Sealer |
last post by:
I have a report that includes 5 different subreports. I'd like to be
able to open this report using a date function (Start Date and End Date).
I'd like all five subreports to show the data from...
|
by: Rachel Suddeth |
last post by:
Is there a way to have the non-selectable dates (those before MinDate and
after MaxDate) draw differently so my users can see right away what dates
aren't allowed? I'm not seeing it...
...
|
by: Dixie |
last post by:
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is...
|
by: pitfour.ferguson |
last post by:
My dbase has the start date and end date of each visit. How can I ask
Access to list the day of the week of the start (easy), end (easy) and,
more importantly, the dates of the visit itself - ie...
|
by: evilcowstare via AccessMonster.com |
last post by:
Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.
There are some searches that I want to apply to my database....
|
by: Jim Carlock |
last post by:
(1) Does PHP provide any way to handle dates prior to 1980?
I know there's problems with Microsoft Windows NT and all Windows
NT operating systems will allow a date prior to 1980 to be placed...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
| |
