how to set the default value of the column when there is null value in stored procedure

Question

Hello I am using stored procedure to get the data from different tables here is my stored procedure.

  SELECT
  ev.Id,
  ev.Title,
  ev.PageUrl,
  ev.FromDate,
  ev.ToDate,
  ev.Isactive,
  CONVERT(char(10), eventtime, 108) as EventTime,
  ev.UserType,
  ev.Street,
  ev.Image,
  ev.Description,
  ev.City,
  ev.CountryCode,
  ev.CategoryId,
  ev.UserId,
  ev.StateCode,
  cm.Name as 'CountryName',
  sm.name as 'StateName',
  asp.FirstName as 'FirstName',
  Cat.Name as 'CategoryName',
  ev.ZipCode
 from events ev
 inner join countrymaster cm on ev.CountryCode=cm.Id
 inner join statemaster sm on ev.StateCode=sm.Id
 inner join category cat on ev.Categoryid=cat.Id
 left join aspnetusers asp on ev.userid=asp.Id
 order by createddate desc  

in seventh column

CONVERT(char(10), eventtime, 108) as EventTime,

I am getting the event time by casting it character but when my event time is null then it throws the error like this

Invalid cast from 'System.String' to 'System.TimeSpan'.

The datatype of event time is time.

So how can i set the default value of eventtime column if there is no value present in it.

Answer 1

Use ISNULL() to check if the eventime is NULL or not.If its null then you can replace a empty string '' or some other date according to your choice.

CONVERT(char(10), ISNULL(eventtime,''), 108) as EventTime  // If you want to replace with empty string

CONVERT(char(10), ISNULL(eventtime,GETDATE()), 108) as EventTime  // If you want to replace with current date

DECLARE @default datetime='2016-12-22 16:43:22.560'
CONVERT(char(10), ISNULL(eventtime,@default), 108) as EventTime  // If you want to relace it with some variable.

Answer 2

Use COALESCE on that column:

CONVERT(char(10), COALESCE(eventtime, GETDATE()), 108) AS EventTime

This would use the current date/time as the default value, though you can use any default you want with this approach.

Answer 3

Use CASE expression like

CASE WHEN eventtime IS NULL THEN GETDATE()
ELSE CONVERT(char(10), eventtime, 108) END AS EventTime,

Answer 4

CONVERT(char(10), isnull(eventtime,getdate()), 108) as EventTime