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  29439 
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: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
