Comparing dates produces an error when a non-date value is encountered

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing dates produces an error when a non-date value is encountered

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-04 : 00:39:08

Glen writes "MS Server 2000 (no SP)
MS SQL 2000 (no SP)
I am trawling through legacy data bringing back records within a date range. Because the data is all varchar I have to use CONVERT to perform the compare successfully which is not a problem.
The problem occures when one of the dates is NULL (or a non-date value might be a better way of describing it to cater for all bad values!)
My statement goes something like this:

SELECT * FROM MyTable WHERE CONVERT(datetime,datestr1) >= CONVERT(datetime,datestr2)

I have tried including the obvious WHERE clause: "NOT datestr1 IS NULL AND NOT datestr2 IS NULL" but this still performs the date comparison and consequentally errors when it finds a value that is not a valid date, such as NULL

Hope you can help

thanks
glen."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-04 : 00:44:53

Glen,

Check out the ISDATE function in BOL...

It returns 1 or 0 (True or False)

DavidM

Tomorrow is the same day as Today was the day before.

Glen
Starting Member

1 Post

Posted - 2002-02-04 : 04:10:27

at the moment i am not using a SP this is just a SQL statement (FWIW: called from an ASP script)
i do plan to re-write it as a SP for efficiency but want to get this working in my statement.
Is there therefore any way of checking for the date in a SQL statement without it bombing?

thanks
glen.

Nazim
A custom title

1408 Posts

Posted - 2002-02-04 : 07:46:51

You can convert your invalid dates to some specific datevalue and then run your query.

eg:
update myTable set fieldname='01-Jan-1900' where isdate(fieldname)=0

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

spock
Starting Member

35 Posts

Posted - 2002-02-04 : 07:53:37

hi

hth

select * from tmptbl where coalesce(join_date,1) >
case isDate(join_date)
when 1 then cast(cast('1-1-1995' as datetime) as int)
else 0
end

kaushik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources