Checking contents of column and replacing contents if first character is a letter

Hi All,

I have come up against a wall which i cannot get over.

I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.

I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.

I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.

Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.

This is how far i have got: casting the varchar column to datetime and comparing.

SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"

Many thanks in advance!

You could adapt your query as follows:

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 0001'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

However, solution two is to add a new column called "Start_DateTime" and populate it with genuine dates. You can then phase out "Start_Date" entirely. It's always easier to data-clean sooner rather than later!

Also, read up on dynamic SQL and SQL injection when you get a minute.

|||

Hi Sohnee,

Thanks for the quick reply!

I've tested your query and I think it is still trying to cast the 'various' entrants as a date, as it returns the following:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Also, how would compare the date selected to the dates in db as i'm unsure of where to place the '>'.

I'll take you advice and start reading!

Thanks again!

|||

Can anybody else help at all?? PLEASEEEEE!!

Ta!

|||

SELECT *FROM table1WHERE CASE WHEN ISDATE(Cast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime))=1 THENCast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime)ELSENULLEND='" + date + "'"

|||

Apologies - in my example I used the minimum date from .NET - which is a bit early for SQL.

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 1900'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

|||

Hi All,

Thanks for all the responses...unfortunately the suggested solutions aren't doing the trick.

I think its still trying convert the varchar 'various' to a date format even when you've asked it set 'various' as '01/01/2010'

Oh well!

|||

Run this query to see if you get any clues back...

SELECT DISTINCT
Start_Date,
ISDATE(Start_Date),
CASE Start_Date
WHEN 'Various' THEN 'Text'
ELSE 'Date'
END = '" + date + "'
FROM
table1

My guess is that you will find other values (as well as 'Various') that aren't dates. - Problem areas will be lines that think they are dates, but that aren't.