+ Reply to Thread
Results 1 to 5 of 5

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

  1. How to check that a date field is null and replace by blank value

    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

  2. Re: How to check that a date field is null and replace by blank value

    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" wrote in message
    news:AAD89445-237F-4C16-8659-6E44E8FF971Datmicrosoftdotcom...
    > 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




  3. Re: How to check that a date field is null and replace by blank value

    > 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" wrote in message
    news:AAD89445-237F-4C16-8659-6E44E8FF971Datmicrosoftdotcom...
    > 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



  4. Re: How to check that a date field is null and replace by blank va

    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

    "Dan Guzman" wrote:

    > > 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" wrote in message
    > news:AAD89445-237F-4C16-8659-6E44E8FF971Datmicrosoftdotcom...
    > > 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

    >
    >


  5. Re: How to check that a date field is null and replace by blank va

    On Mon, 7 Sep 2009 07:10:01 -0700, Ben wrote:

    >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.


    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

+ Reply to Thread