I'm having a fit with a query for a range of dates. The dates are
being returned from a view. The table/field that they are being
selected from stores them as varchar and that same field also stores
other fields from our dynamic forms. The field is called
'FormItemAnswer' and stores text, integer, date, float, etc. Anything
the user can type into one of our web forms. The query looks like,
select distinct [Lease End Date] from
vwFormItem_4_ExpirationDateOfTerm where CONVERT(datetime, [Lease End
Date], 101) >= CONVERT(datetime, '08/03/2003', 101) AND
CONVERT(datetime, [Lease End Date], 101) < CONVERT(datetime,
'09/03/2003', 101)
The underlying view does a simple select based on the particular form
field, lease end date in this case.
This query works fine with 1 date in the where but with two fails with
the dreaded 'syntax error converting to datetime from varchar'.
What appears to be happening is sql is trying to do the CONVERTS
before it filters with the WHERE clause in the view.
I tried using a subquery but it still seems to do the same thing
somehow!
SELECT *
FROM (SELECT *
FROM vwFormItem_4_McD_Lease_4B_ExpirationDateOfTerm
WHERE isdate([Lease End Date]) = 1 ) derived
WHERE (CONVERT(datetime, [Lease End Date], 101) >= CONVERT
(datetime, '#8/3/2003', 101)) AND (CONVERT(datetime, [Lease End Date],
101)
<= CONVERT(datetime, '9/3/2003', 101))
I've tried everything I know to try like doing the CONVERT inside the
view I'm selecting from, doing a datediff, everything. Really goin
crazy here.
Any ideas would be greatly appreciated!
Russell 4 5764
"Russell" <rj******@virtualpremise.com> wrote in message
news:85**************************@posting.google.c om... I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also stores other fields from our dynamic forms. The field is called 'FormItemAnswer' and stores text, integer, date, float, etc. Anything the user can type into one of our web forms. The query looks like,
select distinct [Lease End Date] from vwFormItem_4_ExpirationDateOfTerm where CONVERT(datetime, [Lease End Date], 101) >= CONVERT(datetime, '08/03/2003', 101) AND CONVERT(datetime, [Lease End Date], 101) < CONVERT(datetime, '09/03/2003', 101)
The underlying view does a simple select based on the particular form field, lease end date in this case.
This query works fine with 1 date in the where but with two fails with the dreaded 'syntax error converting to datetime from varchar'.
What appears to be happening is sql is trying to do the CONVERTS before it filters with the WHERE clause in the view.
I tried using a subquery but it still seems to do the same thing somehow!
SELECT * FROM (SELECT * FROM vwFormItem_4_McD_Lease_4B_ExpirationDateOfTerm WHERE isdate([Lease End Date]) = 1 ) derived WHERE (CONVERT(datetime, [Lease End Date], 101) >= CONVERT (datetime, '#8/3/2003', 101)) AND (CONVERT(datetime, [Lease End Date], 101) <= CONVERT(datetime, '9/3/2003', 101))
I've tried everything I know to try like doing the CONVERT inside the view I'm selecting from, doing a datediff, everything. Really goin crazy here.
Any ideas would be greatly appreciated! Russell
Your description is a little unclear, but if [Lease End Date] is not a
datetime column (and since you're converting it explicitly I assume it
isn't), then there may be data in that column which cannot be converted to a
datetime. A common issue is someone entering a date in European format into
a system which can only handle US dates (your code makes this assumption by
using style 101).
You can try this query as a way of checking that:
select [Lease End Date]
from vwFormItem_4_ExpirationDateOfTerm
where isdate([Lease End Date]) = 0
If that query returns rows, then you can investigate and clean them up to be
valid datetimes. The best long-term solution is to make the column a
datetime, validate the input at the client, and then pass it to MSSQL in a
format that is independent of client or server date formats (eg.
'YYYYMMDD'). That may not be something that's possible in your situation, of
course. But with a datetime column, you wouldn't need any conversion in your
query, and an index on the column could be used efficiently.
Simon
Sorry my description is unclear. Here is more info. The underlying
table looks something like this,
FormItemID FormItemAnswer(varchar)
3 5/6/1987
4 John Thompson
5 4/5/1902
6 234 Main St.
7 30115
3 6/7/1984
The FormItemAnswer column is a varchar that stores the users 'answers'
to the items on the web page, like name address, zip
So the underlying view looks like,
SELECT FormItemAnswer as [Lease End Date]
FROM tblFormData
WHERE FormItemID = 3
Then the query that doesn't work calls that view and tries to do a range
on the dates. The dates in the table are all valid, I've checked with a
query just like the one you sent. The problem is that the query is
trying to convert values for other FormItemIDs other than 3! It's like
the execution order is whack. It's trying to do the where clause in the
outer query before it does the where clause in the inner view.
Thanks for the help,
Russell
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Russell Jackson (rj******@virtualpremise.com) writes: Sorry my description is unclear. Here is more info. The underlying table looks something like this,
FormItemID FormItemAnswer(varchar) 3 5/6/1987 4 John Thompson 5 4/5/1902 6 234 Main St. 7 30115 3 6/7/1984
The FormItemAnswer column is a varchar that stores the users 'answers' to the items on the web page, like name address, zip
So the underlying view looks like,
SELECT FormItemAnswer as [Lease End Date] FROM tblFormData WHERE FormItemID = 3
Then the query that doesn't work calls that view and tries to do a range on the dates. The dates in the table are all valid, I've checked with a query just like the one you sent. The problem is that the query is trying to convert values for other FormItemIDs other than 3! It's like the execution order is whack. It's trying to do the where clause in the outer query before it does the where clause in the inner view.
Alright, I think it is time for the standard suggestion. Please post:
o CREATE TABLE statement for your table.
o INSERT statements with sample data giving you the problem.
o The output you are looking for from that sample.
o The query you have so far helps to.
I should point that the kind of processing you are into is a bit fragile.
It is possible that using sql_variant would help a little, but I am
not sure.
You might also consider adding a computed column to the table:
CREATE TABLE tbl (
itemid int NOT NULL,
answer varchar(30) NOT NULL,
dateanswer AS CASE WHEN isdate(answer) = 1
THEN convert(datetime, answer, 101)
ELSE NULL
END)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
>> The table/field [sic] that they are being selected from stores them
as varchar and that same field [sic] also stores other fields [sic]
from our dynamic forms. <<
There is a HUGE difference in a field and column and the moron that
did this to you should be fired for practicing database without a
brain. The field [sic] is called 'FormItemAnswer' and stores text,
integer, date, float, etc. Anything the user can type into one of our
web forms. <<
Fields can do that; this is one reason we gave up file systems for
databases. Columns have one and only one datatype, and optional
constraints, DRI, etc. They maintain their own integrity.
Any ideas would be greatly appreciated! <<
1) Kill or fire the moron who did this. This will save your company
much pain and grief now and in the future.
2) Re-design the datbase correctly before you have a compelte loss of
data integrity and queries that are so complex that you cannot
maintain or port the code. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: carverk |
last post by:
Hello All
I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.
I have 3 Queries, which pull records...
|
by: jwbeaty |
last post by:
Here's a weird one.
I'm running SQL Server 7 and when I run a backup something weird
happens. When I perform the backup via Enterprise Manager by right
clicking on the database I want to...
|
by: Andrew Mayo |
last post by:
This problem was discovered with MSDE2000 SP2 and under WinXP SP2. We
are unsure whether it is more widespread as it has only been seen on
one machine to date.
The problem is related to name...
|
by: Mike Dundee |
last post by:
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. ...
|
by: Alan |
last post by:
Hi everyone! I'm trying to produce a periodic financial report on
projects from various departments. My database is set up with the
tables tblDepartment, tblProjects, tblPeriods, and tblBudgets...
| |
by: Anja |
last post by:
Hi everyone,
I want to write a simple SQL statement that does a comparison on a date
field.
For a simple test, I have the following SQL Statement:
SELECT * FROM Records_T where...
|
by: BurtonBach |
last post by:
I constantly have problems with this and I am hoping someone can explain it to me. Does Access always output text into a query regardless of what the formating of the data is in the table?
I am...
|
by: thewilldog |
last post by:
Hello, I've reviewed the archives here to address the issue, but I'm still running into problems. I've got a table field populated with the record date in text "YYYYMMDD"
To convert it into a...
|
by: cjordan |
last post by:
Hi everyone. I'm new here, and I think I've got a pretty unique problem (haven't found any solution to this anywhere else), but I'm hoping that someone here can help me.
To be honest, I'm not a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |