SQL server MSSMS invalid column name problem

Other SQL Server Topics
1

I created a database table and I'm trying to insert data into it, but I get this error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Datum_narodenia'.

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Datum_narodenia'.`

I wrote this query:

INSERT INTO Zamestnanci (id_Zamestnanca, Meno, Priezvisko, Adresa, Datum_narodenia)
VALUES ('001', 'Filip', 'Pokorný', 'Sladkovičova 336', '12.1.1970'),
('002', 'Ján', 'Malý', 'Hurbanova 36', '27.8.1965'),
('003', 'Milada', 'Gregorová', 'SNP 2', '1.7.1991'),
('004', 'Zuzana', 'Jankovská', 'Okružna 312', '6.10.1959'),
('005', 'Gregor', 'Valek', 'Hlavná 22', '25.6.1985'),

Table I created:

CREATE TABLE Zamestnanci
(
id_Zamestnanca int not null,
Meno varchar(50) null,
Priezvisko varchar(50)null,
Adresa varchar(50) null,
Datum_narodenia date null,
PRIMARY KEY (id_Zamestnanca)
);t

How can I solve this? Thank you for all your answers and advice.

3

I alredy did that :
*INSERT INTO Zamestnanci (id_Zamestnanca , Meno , Priezvisko, Adresa , Datum_narodenia)
VALUES ( 1, 'Filip', 'Pokorný', 'Sladkovičova 336', '19701007'),

(2, 'Ján', 'Malý', 'Hurbanova 36', '19650507'),

(3, 'Milada', 'Gregorová', 'SNP 2', '19910107'),*

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

4

I think this statement will work and makes it easy to undetsand

INSERT INTO Zamestnanci (id_Zamestnanca, Meno, Priezvisko, Adresa, Datum_narodenia)
VALUES ('001', 'Filip', 'Pokorný', 'Sladkovičova 336', '12.1.1970'),
('002', 'Ján', 'Malý', 'Hurbanova 36', '8.27.1965'),
('003', 'Milada', 'Gregorová', 'SNP 2', '1.7.1991'),
('004', 'Zuzana', 'Jankovská', 'Okružna 312', '6.10.1959'),
('005', 'Gregor', 'Valek', 'Hlavná 22', '6.25.1985')

5

Unfortunatelly it didn't work for me

6

try this

select c.name 
from sys.tables t 
join sys.columns c on t.objec_id = c.object_id
where t.name = 'Zamestnanci'

and post back the list of colums names here

7

hi

your Datum_narodenia column format is wrong

your format is dd.mm.yyyy '12.1.1970'

it should be yyyy-mm-dd '1970-01-12'

please change all values
then it will work

god bless

8

try this

INSERT INTO Zamestnanci (id_Zamestnanca, Meno, 
Priezvisko, Adresa, Datum_narodenia)
SELECT id_Zamestnanca, Meno, 
Priezvisko, Adresa, TRY_CONVERT(date, Datum_narodenia,104 ) as Datum_narodenia
FROM (
  VALUES ('001', 'Filip', 'Pokorný', 'Sladkovičova 336', '12.1.1970'),
('002', 'Ján', 'Malý', 'Hurbanova 36', '27.8.1965'),
('003', 'Milada', 'Gregorová', 'SNP 2', '1.7.1991'),
('004', 'Zuzana', 'Jankovská', 'Okružna 312', '6.10.1959'),
('005', 'Gregor', 'Valek', 'Hlavná 22', '25.6.1985')
) AS X(id_Zamestnanca, Meno,Priezvisko,Adresa,Datum_narodenia)

but verify it with

select * From Zamestnanci where Datum_narodenia is null

looks like you have German style dates

German	
4 = dd.mm.yy
104 = dd.mm.yyy
9

your table column Datum_narodenia datatype is date. In mssql date format is 'yyyy-mm-dd' . script should like this

INSERT INTO Zamestnanci (id_Zamestnanca, Meno, Priezvisko, Adresa, Datum_narodenia)
VALUES ('001', 'Filip', 'Pokorný', 'Sladkovičova 336', '1970-01-12'), -- 'yyyy-mm-dd'
('002', 'Ján', 'Malý', 'Hurbanova 36', '1965-08-27'), -- 'yyyy-mm-dd'
('003', 'Milada', 'Gregorová', 'SNP 2', '1991-07-01'), -- 'yyyy-mm-dd'
('004', 'Zuzana', 'Jankovská', 'Okružna 312','1959-10-06'), -- 'yyyy-mm-dd'
('005', 'Gregor', 'Valek', 'Hlavná 22', '1985-06-25') -- 'yyyy-mm-dd'

10

Not only German but all people in middle of europe use this format.

11

The generally accepted practice is to use an unambiguous date format - which would be interpreted by SQL Server correctly all the time.

The problem is that several date formats are subject to the language - and will be interpreted differently. For example - in US English systems the date 01/12/2022 will be interpreted at January 12th, 2022 - but on a British English system it will be interpreted as December 1st, 2022.

However - on both systems the date format YYYYMMDD will always be interpreted correctly. So 20220112 will be interpreted as December 1st, 2022 regardless of the language setting.

The format YYYY-MM-DD is problematic when using the DATETIME data type, as it will be interpreted as YYYY-MM-DD on US English and YYYY-DD-MM on British English. However, for the DATE, DATETIME2 and DATETIMEOFFSET data types it will always be interpreted as YYYY-MM-DD.

1 Like