Writing a NULL datetime to a log


  • DECLARE @Note VARCHAR(500)
    Select @PrevProcDateME = [Previous Process Date] from BatchControlME 
    Set @Note = 'BatchControlME values: Previous Process Date - ' + Cast(@PrevProcDateME  as VARCHAR(20))

    Support SQL 2005, 2008, and 2012

    Having trouble when the SELECT does not return any rows. I guess the @PrevProcDateME is NULL (how can you tell that in the debugger?). How can I get DATETIME into a VARCHAR if it is NULL?

    Thursday, September 26, 2013 10:48 PM


  • NULLs cannot be displayed as a string. However, you can detect NULLs and display a different value instead.

    For example, if you want to display the text "<missing date>" instead of the NULL, you could write this:

    Set @Note = 'BatchControlME values: Previous Process Date - '
    + COALESCE( Cast(@PrevProcDateME  as VARCHAR(20)), '<missing date>')


    Thursday, September 26, 2013 10:53 PM
  • Try

    Set @Note = 'BatchControlME values: Previous Process Date - ' + isnull(Cast(@PrevProcDateME  as VARCHAR(20)),'')

    Many Thanks & Best Regards, Hua Min

    Friday, September 27, 2013 2:03 AM

All replies