CODEWITHSUNDEEP
sql-server-2008t-sql
NealWalters
NealWalters

Reputation: 18227

Simple T-SQL function to convert date to displayable format

I have a strange bug that I cannot resolve (with a one line function).

This code works: 

DECLARE @TestDate datetime = '2013-05-01 23:15:11'
select IsNull(convert(varchar(max), @TestDate, 120), 'null') as 'test1'

Displays: 2013-05-01 23:15:11

CREATE FUNCTION [dbo].[DateOrNullToChar] (@InputDate date)
RETURNS VARCHAR(40)
BEGIN
   return ISNULL(convert(varchar(40),@InputDate, 120),'null'); 
END
select dbo.DateOrNullToChar('2013-05-01 23:15:11') as 'result'

Returns: 2013-05-01 (no time)

I have also tried varchar(max).

The purpose of the function is for something like this: 

Set @ErrorMessage = ' @ArrivalDate=' + dbo.DateOrNullToChar(@ArrivalDate) + 
                    ' @DepartureDate=' + dbo.DateOrNullToChar(@DepartureDate);

If any one value is null, the whole value becomes null. So I want to see the string 'null' when a date has a null value.

Upvotes: 0

Views: 245

Answers (2)

Steve P
Steve P

Reputation: 19397

You need to make the parameter type to be datetime instead of date:

CREATE FUNCTION [dbo].[DateOrNullToChar] (@InputDate datetime)

It's silently converting the string to your date parameter type and thus dropping the time portion.

Upvotes: 2

gbn
gbn

Reputation: 432521

@InputDate should be datetime or datetime2 if you want time to be shown

The clues are in the code...

  • @TestDate datetime
  • @InputDate date

Upvotes: 4

Related Questions