basic : how to insert a date in a table ?

Hi all,

I am new here. I am a student and I am working on SQL Server 2000. I am kind of lost ... I have never used it in the past. I am trying to create a database and one of tables has a date. I couldn't find how to declare a date, I chose DATETIME (I don't know if it is correct).

I have created the following table :

CREATE TABLE SALARIE
( NUMSAL INTEGER PRIMARY KEY,
NOMSAL VARCHAR(15),
PRENOMSAL VARCHAR(15),
ADRESSSAL VARCHAR(30),
VILLESAL VARCHAR(15),
CODEPSAL INTEGER,
DATEEMB DATETIME);

But, I can't find how to insert information in it. Can someone help me please ?

For example, let us say I would like to insert :
NUMSAL : 25
NOMSAL : HUMEAU
PRENOMSAL : Bertrand
ADRESS : 4 rue de Paris
VILLESAL : Paris
CODEPSAL : 75012
DATEEMB : 25/12/2005

I tried this but doesn't work :

INSERT INTO SALARIE VALUES (1, "GERBIER", "Luc", "19 bd Jean Jaurès", "LA TOUR DE SALVAGNY", 69890, "02-01-1990");
Other try 1 :[/b]

screenshot


String and binary datas will be cut/truncated.

Other try 2 :

Screen shot
I typed INSERT.... (we can't see the first letter of INSERT)


Syntax error for the conversion of a date from a string.

[b]Other try 3 :
I was told to use the syntax NUMERIC(numberofinteger)
instaed of INTEGER when creating the table. So I created again the table (deleted it first).

CREATE TABLE SALARIE
( NUMSAL NUMERIC(2) PRIMARY KEY,
NOMSAL VARCHAR(15),
PRENOMSAL VARCHAR(15),
ADRESSSAL VARCHAR(30),
VILLESAL VARCHAR(15),
CODEPSAL NUMERIC(5),
DATEEMB DATETIME);

I was that to insert a date we must put it between "", and that it is "year-day-month".

I made another try ...


The name 'GERBIER' is not allowed in this context. Only contants, expressions, and variables are allowed. Column names are not allowed.

No column is called GERBIER. I don't understand.

Any idea ?

Thanks for your help.

I am under SQL Server 2000 SP2.

First off; don't use " use '.

That should stop it thinking you are refering to columns.

I have installed SQL Server 2000 in English. From an original CD.

So, I could try what you said and I did again what was above. Here are the results with the error messages in English (no wrong or approximative translation).

IN English :
-- Creating the table
CREATE TABLE SALARIE
( NUMSAL NUMERIC(2) PRIMARY KEY,
NOMSAL VARCHAR(15),
PRENOMSAL VARCHAR(15),
ADRESSSAL VARCHAR(30),
VILLESAL VARCHAR(15),
CODEPSAL NUMERIC(5),
DATEEMB DATETIME);

Try 1:

INSERT INTO SALARIE VALUES (1, "GERBIER", "Luc", "19 bd Jean Jaurès", "LA TOUR DE SALVAGNY", 69890, "19900201");

Error message :

Server: Msg 128, Level 15, State 1, Line 1
The name 'GERBIER' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Try 2:

INSERT INTO SALARIE VALUES ("1", "GERBIER", "Luc", "19 bd Jean Jaurès", "LA TOUR DE SALVAGNY", "69890", "19900201");

Error message :
Server: Msg 128, Level 15, State 1, Line 1
The name '1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Try 3:
INSERT INTO SALARIE VALUES ('1', 'GERBIER', 'Luc', '19 bd Jean Jaurès', 'LA TOUR DE SALVAGNY', '69890', '19900201');
Error message :
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Try 4 (removing the ; and using ' instead of ") :

INSERT INTO SALARIE VALUES (1, 'GERBIER', 'Luc', '19 bd Jean Jaurès', 'LA TOUR DE SALVAGNY', 69890, '19900201')

Error message :
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Try 5 (removing the ; but keeping the ") :

INSERT INTO SALARIE VALUES (1, "GERBIER", "Luc", "19 bd Jean Jaurès", "LA TOUR DE SALVAGNY", 69890, "19900201")

Error message :
Server: Msg 128, Level 15, State 1, Line 1
The name 'GERBIER' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Try 6 :
INSERT INTO SALARIE VALUES (1, GERBIER, Luc, 19 bd Jean Jaurès, LA TOUR DE SALVAGNY, 69890, 19900201)

Error message :
Server: Msg 128, Level 15, State 1, Line 1
The name 'GERBIER' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Try this

Hi, actually it works this way :

INSERT INTO SALARIE VALUES ('6','TEST','Beber','1 et ca marche','VILLEURBANNE','92340','22-03-1959')

Or ('03-22-1959') don't remember exactly.

Thank you for your reply.

With the above insert, the section marked in red is more than the 15 character limit you made for that field.