Show empty string when date field is 1 1 1900

Question

I m querying a database like so   SELECT DISTINCT  CASE WHEN CreatedDate    1900-01-01 00 00 00 000  THEN    ELSE CreatedDate END AS CreatedDate FROM LitHoldDetails   lhd CreatedDate is a DateTime field and is non-nullable  I want to display an empty string if the field is the minimum date  1 1 1900   but my CASE statement doesn t work  CreatedDate displays 1900-01-01 00 00 00 000 in my query when that value is in the database  I m using SQL Server 2008 R2  What am I doing wrong

User · Answer

An alternate solution that covers both min (1/1/1900) and max (6/6/2079) dates:

ISNULL(NULLIF(NULLIF(CONVERT(VARCHAR(10), CreatedDate, 120), '1900-01-01'), '2079-06-06'), '').

Whatever solution you use, you should do a conversion of your date (or datetime) field to a specific format to bulletproof against different default server configurations.

See CAST and CONVERT on MSDN: https://msdn.microsoft.com/en-us/library/ms187928.aspx

User · Answer

Use this inside of query  no need to create extra variables   CASE WHEN CreatedDate    19000101  THEN    WHEN CreatedDate    18000101  THEN     ELSE CONVERT CHAR 10   CreatedDate  120          CONVERT CHAR 8   CreatedDate  108  END as  Created Date    Works like a charm

User · Answer

If you CAST your data as a VARCHAR   instead of explicitly CONVERTing your data you can simply SELECT REPLACE CAST CreatedDate AS VARCHAR 20    Jan  1 1900 12 00AM       The CAST will automatically return your Date then as Jun 18 2020 12 46PM fix length strings formats which you can additionally SUBSTRING   SELECT SUBSTRING REPLACE CAST CreatedDate  AS VARCHAR 20    Jan  1 1900 12 00AM      1 11   Output Jun 18 2020

User · Answer

select  ISNULL CONVERT VARCHAR 23   WorkingDate 121      from uv Employee

User · Answer

Two nitpicks   1  Best not to use string literals for column alias - that is deprecated   2  Just use style 120 to get the same value       CASE        WHEN CreatedDate    19000101  THEN           WHEN CreatedDate    18000101  THEN           ELSE Convert varchar 19   CreatedDate  120      END AS  Created Date

User · Answer

When you use a CASE expression  not statement  you have to be aware of data type precedence  In this case you can t just set a DATETIME to an empty string  Try it   SELECT CONVERT DATETIME         One workaround is to present your date as a string   CASE WHEN CONVERT DATE  CreatedDate     1900-01-01  -- to account for accidental time   THEN      ELSE CONVERT CHAR 10   CreatedDate  120              CONVERT CHAR 8   CreatedDate  108  END    Or you could fiddle with the presentation stuff where it belongs  at the presentation tier   Here is an example that works exactly as you seem to want   DECLARE  d TABLE CreatedDate DATETIME    INSERT  d SELECT  19000101  UNION ALL SELECT  20130321    SELECT d   CASE WHEN CreatedDate    19000101    THEN      ELSE CONVERT CHAR 10   CreatedDate  120              CONVERT CHAR 8   CreatedDate  108  END FROM  d    Results   d -------------------                      lt -- empty string 2013-03-21 00 00 00

User · Answer

Try this code   case when CONVERT VARCHAR 10   CreatedDate  103     01 01 1900  then    else CONVERT VARCHAR 24   CreatedDate  121  end  as Date Resolved

[sql-server] Show empty string when date field is 1/1/1900

Examples related to sql-server

Examples related to datetime

Examples related to case