none
remove 1900-01-01 00:00:00.000 to blank

    Question

  • Hello All,

    I having below query where I need to convert default date to blank.

    declare @tbl table (date datetime)
    insert into @tbl select null union all select getdate()
    select isnull(date, '') from @tbl
    

     

    if I'm using below query to conver VARCHAR, then works, but Is there any possibilty to do the same by above query...please help

    declare @tbl table (date datetime)
    insert into @tbl select null union all select getdate()
    select isnull(cast(date as varchar), '') from @tbl
    
    • Changed type KJian_ Wednesday, May 18, 2011 3:04 AM
    Tuesday, May 17, 2011 7:22 AM

Answers

  • "Blank" is a value that exists in the domain of strings. It does not exist in the domain of dates. If you want a blank value, you need to use a string data type.

    Per somewhat funny rules in SQL Server, blank translates to 1900-01-01.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 23, 2011 7:58 AM
    Tuesday, May 17, 2011 7:34 AM
  • No,... you have changed datatype , with datetime you can have NULL not ''
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by KJian_ Monday, May 23, 2011 7:58 AM
    Tuesday, May 17, 2011 7:28 AM
    Answerer

All replies

  • No,... you have changed datatype , with datetime you can have NULL not ''
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by KJian_ Monday, May 23, 2011 7:58 AM
    Tuesday, May 17, 2011 7:28 AM
    Answerer
  • Hallo Yazdani,

    use this example:

    declare @tbl table (date datetime)
    insert into @tbl select null union all select getdate()
    
    select CASE WHEN ISDATE(date) = 1
    	 THEN date
    	 ELSE NULL
    END
    from @tbl
    
    

    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Tuesday, May 17, 2011 7:29 AM
  • "Blank" is a value that exists in the domain of strings. It does not exist in the domain of dates. If you want a blank value, you need to use a string data type.

    Per somewhat funny rules in SQL Server, blank translates to 1900-01-01.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 23, 2011 7:58 AM
    Tuesday, May 17, 2011 7:34 AM
  • Per somewhat funny rules in SQL Server, blank translates to 1900-01-01.


    Hello Erland,

    That's quite "normal": Blank is converted to 0.0 and 0.0 is converted to 1900-01-01:

    SELECT CONVERT(float, '')

    SELECT CONVERT(datetime, 0, 112)

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Tuesday, May 17, 2011 7:41 AM