SQL Server - How to check that a date field is null and replace by blank value

Asked By Ben on 07-Sep-09 05:41 AM
Hello there,

I am trying to query a date field (smalldatetime) with the following query:
select recID, date1, ISNULL(date1,'') from table1.

It returns:
recid   date1
------   ------   ------------------------
1         NULL   1900-01-01 00:00:00

Instead of a blank value, it returns '1900-01-01 00:00:00' . Is it possible
to replace it by ''?

Thanks,
Ben




Uri Dimant replied on 07-Sep-09 05:53 AM
Ben
CREATE TABLE #tbl (c CHAR(1))

INSERT INTO #tbl SELECT NULL

INSERT INTO #tbl SELECT 'A'

--View

SELECT COALESCE(c,'')

FROM #tbl WHERE c IS NULL

--Update

UPDATE #tbl SET c=COALESCE(c,'')

WHERE c IS NULL

SELECT * FROM #tbl
Dan Guzman replied on 07-Sep-09 09:53 AM
ISNULL returns the same data type as the first argument.  Since you are
passing a date, the empty string is converted to a date and this results in
the default date value of '1900-01-01 00:00:00.000'.

Convert the date to a string before the ISNULL (or COALESCE) evaluation so
that the result is a string rather than a date.  However, data formatting
should be handled in the presentation layer rather than in SQL Server, IMHO.

SELECT
recID,
date1,
ISNULL(CONVERT(varchar(19), date1, 120), '')
FROM dbo.table1;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Ben replied on 07-Sep-09 10:10 AM
Yes, this is the one :)

As there are a few pages at the presentation layer calling this same
funtion, formatting the data at sqlserver will provide more convenience.

Thanks for all the replies,
Ben
Hugo Kornelis replied on 07-Sep-09 02:31 PM
Not to clients who expect dates to be displayed in the format they chose
in their computers' locale settings...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis