SQL 将 NULL 日期时间转换为空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26282007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert NULL datetime to Blank
提问by Emily
I would like to convert a datetime field to varchar so I can show a blank or a message when the date is NULL. This is what I have so far:
我想将日期时间字段转换为 varchar，以便在日期为 NULL 时显示空白或消息。这是我到目前为止：
select
isnull(us.Date,0) as USDate,
isnull(au.Date,0) as AUDate
from ustable us
left join autable au
on us.column=au.column
Right now this is showing:
现在这显示：
USDATE
2014-10-24 10:29:07.450
1900-01-01 00:00:00.000
I would like to be able to make the "1900-01-01 00:00:00.000" varchar so I can write a message or show a true blank.
我希望能够制作“1900-01-01 00:00:00.000” varchar 以便我可以写一条消息或显示真正的空白。
回答by Krishnraj Rana
You can use CaseAND CONVERTlike this -
您可以像这样使用CaseAND CONVERT-
SELECT
CASE WHEN us.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), us.Date, 121) END AS USDate,
CASE WHEN au.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), au.Date, 121) END AS AUDate
FROM ustable us
left join autable au
on us.column=au.column
回答by Lewis
The simplest line of code I've found for what you're trying to accomplish is as follows.
我为您要完成的任务找到的最简单的代码行如下。
ISNULL(CONVERT(VARCHAR(30),us.Date,121),'') as USDate,
ISNULL(CONVERT(VARCHAR(30),au.Date,121),'') as AUDate,
I have tested this and verified that it works.
我已经测试过这个并验证它有效。
回答by kttii
You can use the NVL() function ...
您可以使用 NVL() 函数...
You can use the NVL function to convert an expression that evaluates to NULL to a value that you specify. The NVL function accepts two arguments: the first argument takes the name of the expression to be evaluated; the second argument specifies the value that the function returns when the first argument evaluates to NULL. If the first argument does not evaluate to NULL, the function returns the value of the first argument.
您可以使用 NVL 函数将计算结果为 NULL 的表达式转换为您指定的值。NVL 函数接受两个参数：第一个参数是要计算的表达式的名称；第二个参数指定当第一个参数计算为 NULL 时函数返回的值。如果第一个参数的计算结果不是 NULL，则函数返回第一个参数的值。
For example:
例如：
select
nvl(us.Date,'') as USDate,
nvl(au.Date,'') as AUDate
from ustable us
left join autable au
on us.column=au.column
Or put anything you want as the Default:
或者把你想要的任何东西作为默认值：
select
nvl(us.Date,'this was a NULL') as USDate,
nvl(au.Date,'this was a NULL') as AUDate
from ustable us
left join autable au
on us.column=au.column
回答by Alex W
To do it in SQL:
要在 SQL 中执行此操作：
Change your SELECTstatement to incorporate a WHEN...THENclause:
更改您的SELECT语句以包含WHEN...THEN子句：
CASE us.Date
WHEN '1900-01-01 00:00:00.000' THEN ''
WHEN NULL THEN ''
ELSE us.Date
END as USDate,
CASE au.Date
WHEN '1900-01-01 00:00:00.000' THEN ''
WHEN NULL THEN ''
ELSE au.Date
END as AUDate
If you have front-end code, then you could just implement the functionality on the front-end without changing your SQL code.
如果您有前端代码，那么您只需在前端实现功能，而无需更改 SQL 代码。
回答by Nicholas Carey
Given a column defined like this:
给定一个这样定义的列：
date_of_birth datetime null
You can simply say
你可以简单地说
select date_of_birth = coalesce( convert(varchar(32),date_of_birth) , '' )
from some_table_with_a_nullable_datetime_column
回答by DeadZone
Try something like this...
尝试这样的事情......
select
CONVERT(varchar(50), isnull(us.Date,0)) as USDate,
CONVERT(varchar(50), isnull(au.Date,0)) as AUDate
from ustable us
left join autable au
on us.column=au.column
You'll want to change the size of the varchar depending upon your date format. Also, CONVERT takes a third parameter that will allow you to change the format of your string.
您需要根据日期格式更改 varchar 的大小。此外，CONVERT 采用第三个参数，允许您更改字符串的格式。
回答by raghavendra khatavkar
select null USDate, NUll AUDate
from ustable us
left join autable au on us.column=au.column
It's work try it, it will show column value should be null.
它的工作尝试一下，它会显示列值应该为空。
