How to format the date in SQL Server?-CodePudding

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');

CodePudding user response：

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');

yyyyMMdd

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

CodePudding user response：

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