May 22, 2009 at 8:19 am
What I need to do is return an empty string for a date field if the formid is either 11 or 15.
The fields are NULL as of now. That's why I am using the IsNull function.
In the select statement I am doing this.
SELECT.....
, Case
WHEN vr.FormId = 15 then IsNULL(CAST(vr.DiaryDate as varchar), '')
WHEN vr.FormId = 11 then IsNULL(CAST(vr.DiaryDate as varchar), '')
ELSE isNull(vr.DiaryDate,'1/1/1900')
End
as 'DiaryDate'
FROM table
I have also tried to use the convert with no luck. I am able to return a NULL if I do this.
SELECT
, Case
WHEN vr.FormId = 15 then NULL
WHEN vr.FormId = 11 then NULL
ELSE isNull(vr.DiaryDate,'1/1/1900')
End
as 'DiaryDate'
FROM table
But if I use IsNULL I get a return date of '1/1/1900'
This is all inside a stored procedure. Any thoughts or suggestions would be appreciated.
______________________________
AJ Mendo | @SQLAJ
May 22, 2009 at 9:16 am
That is because SQL is converting the string to a datetime so the datatypes are compatible in the case statement. If you convert the date to a varchar, the query will return an empty string.
Declare @tst DateTime
Set @tst = '1/1/2008'
Select
Case when 1=1 THEN
''
Else
@tst
End
--Convert date to varchar
Select
Case when 1=1 THEN
''
Else
Convert(varchar(10),@tst,101)
End
May 22, 2009 at 1:59 pm
Thanks
______________________________
AJ Mendo | @SQLAJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply