日期字段为1/1/1900时显示空字符串 [英] Show empty string when date field is 1/1/1900
问题描述
我正在查询数据库:
SELECT DISTINCT
CASE WHEN CreatedDate = '1900-01-01 00:00:00.000' THEN '' ELSE CreatedDate END AS CreatedDate
FROM LitHoldDetails
lhd.CreatedDate是一个DateTime字段,不可为空。如果字段是最小日期(1/1/1900),我想显示一个空字符串,但是我的CASE语句不起作用;当该值在数据库中时,CreatedDate在我的查询中显示1900-01-01 00:00:00.000。我正在使用SQL Server 2008 R2。我做错了什么?
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?
推荐答案
当您使用 CASE
表达式(不是语句),您必须知道数据类型优先级。在这种情况下,您不能将 DATETIME
设置为空字符串。尝试一下:
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;
结果:
d
-------------------
<-- empty string
2013-03-21 00:00:00
这篇关于日期字段为1/1/1900时显示空字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!