Prev: IRR calculation
Next: Calculate Differences in Closing Balances
From: Ben on 7 Sep 2009 05:41
Hello there,

I'm 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
From: Uri Dimant on 7 Sep 2009 05:53
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



"Ben" <Ben(a)discussions.microsoft.com> wrote in message
news:AAD89445-237F-4C16-8659-6E44E8FF971D(a)microsoft.com...
> Hello there,
>
> I'm 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


From: Dan Guzman on 7 Sep 2009 09:53
> Instead of a blank value, it returns '1900-01-01 00:00:00' . Is it
> possible
> to replace it by ''?

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" <Ben(a)discussions.microsoft.com> wrote in message
news:AAD89445-237F-4C16-8659-6E44E8FF971D(a)microsoft.com...
> Hello there,
>
> I'm 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

 | 
Pages: 1
Prev: IRR calculation
Next: Calculate Differences in Closing Balances