Trouble with returning date as an empty string

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  May 22, 2009 at 8:19 am

  #131213

  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

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  May 22, 2009 at 9:16 am

  #998929

  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

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  May 22, 2009 at 1:59 pm

  #999149

  Thanks

  ______________________________
  AJ Mendo | @SQLAJ