title: SQL Server Msg 241, Level 16, State 1, Conversion failed when converting date and/or time from character string
body: <p>I am getting this error when executing a <code>stored procedure</code> that I have created</p>
  
  <blockquote>
    <p>Msg 241, Level 16, State 1, Procedure UDP_INITIAL_CUSTOMER_DIM_POPULATION, Line 28<br>
    Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>The code for the procedure is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and the code to create the <code>customer_dim</code> table is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>