Luke L Luke L - 7 months ago 89
SQL Question

SQL Server Msg 241, Level 16, State 1, Conversion failed when converting date and/or time from character string

I am getting this error when executing a stored procedure that I have created


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.


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_dim
table is:

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. Please Help!

Answer

The error is caused by the default value for the expiry_date. Running

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'
Comments