SQL queries by data type. 1

[Brasil221]

I am currently developing a webpage in ASP using a lot of ADO, and therefore, SQL. This would be all fine and dandy, if it weren't for the insanely convoluted database system we have at my workplace.

--Skip this part if you don't want the backstory--

You see, my boss came up with this brilliant "content management system" which regular Joes could use to update their webpages without knowing a single bit of code. the problem is, the database solution he came up with is the most backasswards thing I think I've ever seen.

I have to query several tables to get the info I need. Got that, INNER JOIN, done. the issue is, to make the database as confusing as possible, my boss is using multiple data types in each column. I have filtered the records by their "ItemID," so I'm only getting the relevant ones, but these records contain, all in the same column, all sorts of things like, dates, names, descriptions, etc. I want to pick out, from the records that match my "ItemID," only the ones whose "SubItemContent" contains a date. Is that possible?

--Ok, start reading again--

Too long, didn't read?
here's what I want the query to do (simplified and in my non-SQL lingo):

SELECT * FROM Modules WHERE ItemID = 123 AND SubItemContent datatype = date

Soo... How do I do that?

 

[blom0344]

Tricky..
If the possible date is stored as free text, then you have no control over the exact format used when the date is entered. Could be '2010-05-27' or '2010/mai/27' or any other entered format. text fields should be used for just that. Dates should be stored in a datetype field or they will be lost in the crowd..

Ties Blom

 

[Brasil221]

There is a glimmer of hope... The dates are selected from a calendar by the user, rather than input into a textbox, so they're all uniformly in a "DD/MM/YYYY" format. I tried querying SubItemContent LIKE "%/%/%" but it still wouldn't let me ODER BY SubItemContent, stating that "The text, ntext, and image data types cannot be used in an ORDER BY clause."

Also, restructuring the database at this point isn't really an option since hundreds of modules currently use it successfully....

 

[paulkeys]

Not sure if i'm missing something here but would the ISDATE() function not achieve what you need....


SELECT * FROM Modules WHERE ItemID = 123 AND ISDATE(SubItemContent)=1

 

[Brasil221]

I do believe that would have worked. However, we found a fix that is working now (and if it ain't broke, don't fix it).

If anyone else stumbles across this and is curious, here's the fix:

SELECT * FROM Modules WHERE ItemID = 123 AND SubItemContent cast (cast (SubItemContent as nvarchar) as date)

It had problems converting straight to date, but that little workaround is working beautifully.

See it in action at biohio.osu.edu (as of right now it's not totally finished yet, but at least operational). The "What's Happening" section is the one that gave us the heartache.