日期列中的空白值在运行 SELECT 语句时返回为 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.

是否可以将 1900-01-01 替换为 null 并以 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 值，由于您正在从字符串数据类型转换为日期，因此字符串数据类型可以有空字符串但日期数据类型不能有空日期值，它可以有日期值或 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 语句时返回为 1900/01/01的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！