运行SELECT语句时，Date列中的空值返回为1900/01/01 [英] Blank values in Date column returning as 1900/01/01 on running SELECT statement

问题描述

列[PAYOFF DATE]有一些空白值和一些值，以mm / dd / yy格式。

The column [PAYOFF DATE] has some blank values and some values in mm/dd/yy format.

我必须用' - '替换'/'并将日期作为yyyy-mm-dd返回。以下查询正在做。问题是，对于所有空白值，我得到的结果为1900-01-01。

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.

是否可以用null替换1900-01-01并返回其他有效的日期值按照yyyy-mm-dd格式？

Is it possible to replace 1900-01-01 with null and return other valid date values as is in yyyy-mm-dd format?

我正在使用SQL Server。

I am using SQL Server.

SELECT
cast(replace(a.[PAYOFF DATE],'/','-') as date) 
FROM MTG a

推荐答案

您不需要像您在问题中显示的那样进行字符串操作。如果您将日期存储在 mm / dd / yyyy 格式中，只能将其转换为DATE。

You dont need to do the string manipulation as you have shown in your question. If you have dates stored in mm/dd/yyyy format just cast it as DATE.

SELECT cast(a.[PAYOFF DATE] AS DATE) 
FROM MTG a 

对于 1900-01-01 值，由于您将字符串数据类型转换为Date，所以String数据类型可以具有空字符串，但Date数据类型不能为空日期值，它可以具有日期值或 NULL 值。

For 1900-01-01 values, 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 NULL value.

因此，您需要将空字符串转换为空值，然后再将其转换为日期。 1900-01-01只是sql server为您输入的默认值，因为Date数据类型不能有空值。

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.

您可以通过执行此操作避免使用此SQL Server默认值。

You can avoid having this sql server default value by doing something like this.

SELECT cast(NULLIF(a.[PAYOFF DATE],'') AS DATE) 
FROM MTG a 

这篇关于运行SELECT语句时，Date列中的空值返回为1900/01/01的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！