Weird Date Query Problems

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

re: Weird Date Query Problems

"Russell" <rjackson@virtualpremise.com> wrote in message
news:85739638.0309150808.4298569d@posting.google.c om...[color=blue]
> 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[/color]

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

re: Weird Date Query Problems

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!

re: Weird Date Query Problems

Russell Jackson (rjackson@virtualpremise.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Weird Date Query Problems

>> 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.
[color=blue][color=green]
>> The field [sic] is called 'FormItemAnswer' and stores text,[/color][/color]
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.
[color=blue][color=green]
>> Any ideas would be greatly appreciated! <<[/color][/color]

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.