SQL 日期列中的空白值在运行 SELECT 语句时返回为 1900/01/01
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22973154/
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
Blank values in Date column returning as 1900/01/01 on running SELECT statement
提问by Reeya Oberoi
The column [PAYOFF DATE] has some blank values and some values in mm/dd/yy format.
[PAYOFF DATE] 列有一些空白值和一些 mm/dd/yy 格式的值。
I have to replace '/' with '-' and return the date as yyyy-mm-dd. The below query is doing it. The problem is that for all blank values, I am getting results as 1900-01-01.
我必须用 '-' 替换 '/' 并将日期返回为 yyyy-mm-dd。下面的查询正在这样做。问题是对于所有空白值，我得到的结果为 1900-01-01。
Is it possible to replace 1900-01-01 with null and return other valid date values as is in yyyy-mm-dd format?
是否可以将 1900-01-01 替换为 null 并以 yyyy-mm-dd 格式返回其他有效日期值？
I am using SQL Server.
我正在使用 SQL Server。
SELECT
cast(replace(a.[PAYOFF DATE],'/','-') as date)
FROM MTG a
回答by M.Ali
You dont need to do the string manipulation as you have shown in your question. If you have dates stored in mm/dd/yyyyformat just cast it as DATE.
您不需要像问题中所示那样进行字符串操作。如果您以mm/dd/yyyy格式存储日期，只需将其转换为 DATE。
SELECT cast(a.[PAYOFF DATE] AS DATE)
FROM MTG a
For 1900-01-01values, since you are converting from a string data type to Date, String datatype can have Empty strings but Date datatype cannot have empty date values, It can have either a date value or NULLvalue.
对于1900-01-01值，由于您要从字符串数据类型转换为日期，因此字符串数据类型可以具有空字符串，但日期数据类型不能具有空日期值，它可以具有日期值或NULL值。
Therefore you need to convert the empty string to nulls before you convert it to date. 1900-01-01 is just a default value sql server puts in for you because Date datatype cannot have an empty value.
因此，您需要先将空字符串转换为空值，然后再将其转换为日期。1900-01-01 只是 sql server 为您设置的默认值，因为 Date 数据类型不能有空值。
You can avoid having this sql server default value by doing something like this.
您可以通过执行此类操作来避免使用此 sql server 默认值。
SELECT cast(NULLIF(a.[PAYOFF DATE],'') AS DATE)
FROM MTG a
回答by Raj
CASE WHEN [Pay Date] = '' THEN NULL ELSE TRY_CONVERT(DATE, [Pay Date]) END
CASE WHEN [Pay Date] = '' THEN NULL ELSE TRY_CONVERT(DATE, [Pay Date]) END
