Query does not return data

Question

Post reply

Query does not return data

mtz676

SSCrazy Eights

Points: 9233
More actions
November 2, 2010 at 10:26 am

#230621

SELECT *
FROM ABCD where ColA like '%AB CD EF GH%' or ColA like DE FD FG GH%'
and
WrittenDate
between
'1/1/2000'
and
'1/1/2011
'and
ABCD.ColB='5A'
There are 100000 rows in this table ABCD and several rows which satisfy the conditions in this query but it does not return any results.
SELECT *
FROM ABCD where
ABCD.ColB='5A'........This returns data.
SELECT *
FROM ABCD
where
ColA like'%AB CD EF GH%' or ColA like DE FD FG GH%'....This returns data.
Looks like I am having issues with the date column...
My ABCD table definition...
CREATE TABLE [dbo].[ABCD](
[WrittenDate] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
I cannot change the table definition now.....I want the writtendate to be [WrittenDate] [varchar](8000) only.
Responses are appreciated.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

A datetime column stored as varchar(8000)?

Good luck.

Seriously, convert that column into datetime. You won't regret.

There's no safe way to query / update data stored in the wrong format.

-- Gianluca Sartori

GSquared SSC Guru Points: 260824 More actions · Answer 2

You're going to have to convert the column to DateTime in the Where clause.

That'll make the query work as expected. It'll be SLOW if the table has any serious number of rows, but it'll work.

Then I recommend a "boot to the head" treatment for whomever made the decision to store dates as text. (Appologies if that was you. Now, once you accept the appology, kick yourself in the head.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95461 More actions · Answer 3

Are you sure that the dates are stored that way?

Could they be 01/01/2000? (zero padded days/months)

Or 01-01-2000? (zero padded days/months & different separator)

or 01.01.2000? (zero padded days/months & different separator)

or 01/1/2000? (zero padded months (or is it days? what is your DateFormat setting?)

or 01 Jan 2000? (Month names spelled out)

or Jan 01 2000?

or ...

Are you starting to see the problems with using character fields to store dates?

You might want to try:

WHERE Convert(Datetime, WrittenDate) between '1/1/2000' and '1/1/2011'

Edit: but, don't be surprised if some values don't convert due to garbage data!!!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 4

Even a convert will fail if someone has put non-date data in your column. You should probably do an ISDate() check on the column to weed out non date values first.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What does this return?

SELECT TOP (100) DISTINCT WrittenDate FROM ABCD

where (ColA like '%AB CD EF GH%' or ColA like 'DE FD FG GH%')

and ABCD.ColB='5A'

btw, you have missing brackets in your where clause. AND takes precedence over OR, so you need to wrap brackets around the ORs (as I've done) if what I think you're trying is what you actually want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass