Error inserting date into date type column

Question

create table DogLicense ( License int IDENTITY(1,1) PRIMARY KEY, Expires date NOT NULL CHECK(Expires > '1990-01-01') , Sex char(2) NOT NULL CONSTRAINT check_Sex_M_F_NM_SF CHECK (Sex IN ('M','F','NM','SF')), PetName char(50) NOT NULL , Breed char(50) , OwnerLastName char(50) NOT NULL , OwnerFirstName char(50) NOT NULL , Address char(50) , Zip Char(5) NOT NULL CHECK(Zip >= 99201 and zip <= 99212), Phone char(10) , )

So I have created the table above and when attempting to enter my first row of data I get the error

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

My insert into statement is as follows. From everything I have read so far I'm using the correct format. Any idea why this isn't working?

insert into DogLicense values ('2023-21-06','NM', 'Rosco', 'St. Bernard','Freeman','Mark', '123 Medow Ln.','99207','5095551212' )

I have tried not using quotes but I get

`Msg 206, Level 16, State 2, Line 1 Operand type clash: int is incompatible with date'

Answer 1

I believe you are trying to insert the date as YYYY-DD-MM instead of YYYY-MM-DD.

Try

insert into DogLicense  values ('2023-06-21','NM', 'Rosco', 'St. Bernard','Freeman','Mark', '123 Medow Ln.','99207','5095551212' )

Answer 2

SQL Server likes his date in yyyymmdd as string to be inserted

create table DogLicense 
  ( License int IDENTITY(1,1) PRIMARY KEY
  , Expires date NOT NULL  CHECK(Expires > '1990-01-01') 
  ,  Sex char(2) NOT NULL  CONSTRAINT check_Sex_M_F_NM_SF CHECK (Sex IN ('M','F','NM','SF'))
  ,  PetName char(50) NOT NULL 
  ,  Breed char(50) 
  ,  OwnerLastName char(50) NOT NULL 
  ,  OwnerFirstName char(50) NOT NULL 
  ,  Address char(50) 
  ,  Zip Char(5) NOT NULL  CHECK(Zip >= 99201 and zip <= 99212)
  ,  Phone char(10) )

insert into DogLicense  values 
  ('20230621'
  ,'NM'
  , 'Rosco'
  , 'St. Bernard'
  ,'Freeman','Mark'
  , '123 Medow Ln.'
  ,'99207'
  ,'5095551212' )

1 rows affected

fiddle