SQL Server 消息 241，级别 16，状态 1，从字符串转换日期和/或时间时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36879088/
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
SQL Server Msg 241, Level 16, State 1, Conversion failed when converting date and/or time from character string
提问by Luke L
I am getting this error when executing a stored procedurethat I have created
执行stored procedure我创建的a 时出现此错误
Msg 241, Level 16, State 1, Procedure UDP_INITIAL_CUSTOMER_DIM_POPULATION, Line 28
Conversion failed when converting date and/or time from character string.
消息 241，级别 16，状态 1，过程 UDP_INITIAL_CUSTOMER_DIM_POPULATION，第 28 行
从字符串转换日期和/或时间时转换失败。
The code for the procedure is:
该过程的代码是：
CREATE PROCEDURE UDP_INITIAL_CUSTOMER_DIM_POPULATION
AS
BEGIN
SET NOCOUNT ON
EXEC UDP_REMOVE_NULLS_FROM_CUSTOMER_STG
INSERT INTO dbo.customer_dim(customer_no, first_name, middle_name, last_name,
street_number, street_name, po_address, zip_code, city,
region, country)
SELECT DISTINCT
CAST(customer_no AS VARCHAR),
first_name,
middle_name,
last_name,
street_number,
street_name,
po_address,
zip_code,
city,
region,
country
FROM
dbo.customer_stg
PRINT 'Initial customer data in place!'
SET NOCOUNT OFF
END
and the code to create the customer_dimtable is:
创建customer_dim表的代码是：
CREATE PROCEDURE dbo.UDP_CREATE_CUSTOMER_DIM
AS
BEGIN
IF OBJECT_ID('customer_dim', 'U') IS NOT NULL
BEGIN
DROP TABLE customer_dim
END
CREATE TABLE [dbo].[customer_dim]
(
[customer_sk] INT IDENTITY (1,1) PRIMARY KEY,
[customer_no] [varchar](50) NULL,
[first_name] [varchar](50) NULL,
[middle_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[customer_name] [varchar](50) NULL,
[street_number] [varchar](50) NULL,
[street_name] [varchar](50) NULL,
[customer_address] [varchar](100) NULL,
[po_address] [varchar](50) NULL,
[zip_code] [varchar](50) NULL,
[city] [varchar](50) NULL,
[region] [varchar](50) NULL,
[country] [varchar](50) NULL,
[effective_date] DATE NULL DEFAULT GETDATE(),
[expiry_date] DATE NULL DEFAULT '999-12-31'
) ON [PRIMARY]
END
I have tried looking for a solution but because I am not trying to do anything on the date or time I cannot find anything that is making sense to me.
我曾尝试寻找解决方案，但因为我不想在日期或时间做任何事情，所以我找不到任何对我有意义的事情。
回答by Felix Pamittan
The error is caused by the default value for the expiry_date. Running
该错误是由 的默认值引起的expiry_date。跑步
SELECT CAST('999-12-31'AS DATE)
will produce an error:
会产生一个错误：
Conversion failed when converting date and/or time from character string.
从字符串转换日期和/或时间时转换失败。
What you want is
你想要的是
[expiry_date] DATE NULL DEFAULT '9999-12-31'
回答by sandeep rawat
Default value should be converted to the data type of column by query executor.
查询执行器应将默认值转换为列的数据类型。
Date data type requires data in " YYYY-mm-dd" fromat
日期数据类型需要“ YYYY-mm-dd”中的数据 fromat
Luke problem is with your default vale for date data type.
Luke 的问题在于日期数据类型的默认值。
While sql is trying to convert /cast yyy-mm-dd it fails to convert it to date
当 sql 尝试转换 /cast yyy-mm-dd 时，它无法将其转换为日期
