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?
This question is related to
sql-server
datetime
case
Try this code
(case when CONVERT(VARCHAR(10), CreatedDate, 103) = '01/01/1900' then '' else CONVERT(VARCHAR(24), CreatedDate, 121) end) as Date_Resolved
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
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.
If you CAST
your data as a VARCHAR()
instead of explicitly CONVERT
ing 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
select ISNULL(CONVERT(VARCHAR(23), WorkingDate,121),'') from uv_Employee
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]
Source: Stackoverflow.com