How to format the date in SQL Server?

Question

I received this error.

Error Code: 1292. Incorrect date value: '9/28/1989' for column 'Birthdate' at row 1 0.000 sec.

How can I fix this error rather than edit the values into the format MySQL accepts?

CREATE TABLE Patients (
    Patient_ID integer PRIMARY KEY NOT NULL,
    L_Name varchar (10) NOT NULL,
    F_Name varchar (15) NOT NULL,
    Birthdate date NOT NULL,
    Telephone text (15) NOT NULL,
    Patient_Status Varchar (20) NOT NULL);

INSERT INTO Patients VALUES ( '2116', 'Muhammed', 'Khalis', '9/28/1989', '+1868-622-8322', 'Outpatient');
INSERT INTO Patients VALUES ( '2117', 'Gueverra', 'Leanna', '1/24/2005', '+1868-321-4128', 'Outpatient');
INSERT INTO Patients VALUES ( '2118', 'Hunte', 'Riley', '3/8/1944', '+1868-293-2782', 'Outpatient')
INSERT INTO Patients VALUES ( '2117', 'Gueverra', 'Leanna', '1/24/2005', '+1868-321-4128', 'Outpatient');
INSERT INTO Patients VALUES ( '2118', 'Hunte', 'Riley', '3/8/1944', '+1868-293-2782', 'Outpatient');

You've tagged SQL Server, but mention MySQL in your question - which is it? — Dale K, Jun 21 '22 at 02:26
Note, `VALUES` is plural, i.e. you can use it to declare multiple rows, so you can use a single `INSERT` statement with multiple rows in the `VALUES` clause. Also you should be explicitly naming your columns in your `INSERT` statement. — Dale K, Jun 21 '22 at 03:33
`Error Code: 1292. Incorrect date value:` is MySQL which is different to 'MS SQL Server" either the error message shown is wrong or the title and tag of the question is wrong. Please choose the right tag and edit question to suit. — Paul Maxwell, Jun 21 '22 at 03:54
https://stackoverflow.com/questions/14625191/error-code-1292-incorrect-date-value-mysql — Mitch Wheat, Jun 21 '22 at 04:00

score 0 · Answer 1 · edited Jun 21 '22 at 03:33

0

For SQL Server you can try the following:

SQL

yyyy-MM-dd

INSERT INTO Patients values ( '2116', 'Muhammed', 'Khalis', '1989-09-28', '+1868-622-8322', 'Outpatient');

Or

yyyyMMdd

INSERT INTO Patients values ( '2116', 'Muhammed', 'Khalis', '19890928', '+1868-622-8322', 'Outpatient');

edited Jun 21 '22 at 03:33

Dale K

25,246
15
42
71

answered Jun 21 '22 at 02:34

miriamka

459
5
9

2

Best practice is to explicitly name the columns in the INSERT statement. – Dale K Jun 21 '22 at 03:34
3

And YYYYMMDD is the only string representation that is 100% going to be interpreted correctly no matter what language settings you have. – Sean Lange Jun 21 '22 at 03:43
whilst YYYYMMDD is safest literal in SQL Server, the stated error message 1292 is from MySQL – Paul Maxwell Jun 21 '22 at 03:49

Paul Maxwell · Answer 2 · 2022-06-21T03:56:52.790

Error 1292 is a MySQL error.

In MySQL you can use YYYY-MM-DD or YYYYMMDD or STR_TO_DATE()

e.g.

 create table test (
 datecol date)


 insert into test (datecol) values ('2022-10-16');
 insert into test (datecol) values ('20221016');
 insert into test (datecol) values (str_to_date('10/16/2022','%m/%d/%Y"'));

 select * from test

| datecol    |
| :--------- |
| 2022-10-16 |
| 2022-10-16 |
| 2022-10-16 |

 insert into test (datecol) values ('10/16/2022')

Incorrect date value: '10/16/2022' for column 'datecol' at row 1

db<>fiddle here

How to format the date in SQL Server?

2 Answers2