Isdate function in a SQL string 1

[CompCodeBoy]

SET @strSQL = @strSQL + ' WHERE Isdate(Date_Requested) <= ''0'''

The above is not producing the desired result in a sql string but it does in query analyzer.

I'm basically tring to look for dates with a null value.
I got this

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900 '''

which works fine but I need to OR the above with WHERE Isdate(Date_Requested) <= ''0'''

any help would be appreciated

RA

 

[gmmastros]

I'm basically tring to look for dates with a null value.

SET @strSQL = @strSQL + ' WHERE Date_requested Is NULL'


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Um... ISDATE() returns integer - either 0 or 1. And if tested value is NULL, it returns 0.

'' and < are not needed... exactly what do you want to check?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

Do you want to show records that are null (for the date_requested) also?

If yes then add

date_requested is null

Isdate determines whether an input expression is a valid date and returns 0 or 1. If you use where isdate(value) = 0 you might end up with all non date values in the table (along will NULL), I am not sure if that is your requirement?

Anyways, Try just adding the code below to your dynamic sql and see what you get:

or isdate(date_requested) = 0

Regards,
AA

 

[CompCodeBoy]

This is part of a stored procedure that works fine. It got SQL string in it.

What I want to do is check for null values in the date_requested field.

I got this:
SET @strSQL = 'SELECT *,team.team_autonumber,team_name,dept.department_No,dept.department_Name
FROM OpenIssues inner join team ON team.team_autonumber=openissues.team_assigned_to inner join Dept ON openissues.Req_Dept_No=Dept.department_No'

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900 '''

EXEC(@strSQL)

Works perfectly.

I want also to check for NULL values in date_requested field

that is trying to OR isDate(date_requested) = 0 to date_requested>''01/01/1900 '''

 

[CompCodeBoy]

Guys,

I know Isdate(blabla)=0 works in query analyzer

but how do youmake it work part of a sql string ?

Thanks

 

[amrita418]

I do not understand your problem.

Did you try either of these as suggested??

SET @strSQL = 'SELECT *,team.team_autonumber,team_name,dept.department_No,dept.department_Name
FROM OpenIssues inner join team ON team.team_autonumber=openissues.team_assigned_to inner join Dept ON openissues.Req_Dept_No=Dept.department_No'

SET @strSQL = @strSQL + ' WHERE  date_requested>''01/01/1900'' or date_requested is null'

EXEC(@strSQL)

OR

SET @strSQL = 'SELECT *,team.team_autonumber,team_name,dept.department_No,dept.department_Name
FROM OpenIssues inner join team ON team.team_autonumber=openissues.team_assigned_to inner join Dept ON openissues.Req_Dept_No=Dept.department_No'

SET @strSQL = @strSQL + ' WHERE  date_requested>''01/01/1900'' or isdate(date_requested) = 0'

EXEC(@strSQL)

What were your findings?

 

[CompCodeBoy]

I tried that before and it ignores the or isdate(date_requested) = 0'

That is

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900'' or isdate(date_requested) = 0'

gives the same result as

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900''

I have null values in date_requested

 

[amrita418]

Did you have any luck with date_requested is null?

Both the syntax should work. Infact, I double checked and it works fine for me.

What is the datatype of your column (date_requested)?

 

[CompCodeBoy]

No it did not work.

It is a smalldatetime. Do I have to cast it?

 

[amrita418]

Can you post some sample data?

Also, try running the query without dynamic sql and see if you get the right results.

 

[CompCodeBoy]

It does work without dynamic sql. Did it work for you in dynamic sql???

 

[amrita418]

try replacing the where clause with the code below:

isnull(rec_update_dt, ''01/01/1901'') > ''01/01/1900'''

if the code above does not give you right results, post your complete code and some sample data (with nulls for date_requested)?