0

I'm writing a simple insert query. Here's the table:

+--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | course | varchar(50) | YES | | NULL | | | sdate | date | YES | | NULL | | | edate | date | YES | | NULL | | +--------+-------------+------+-----+---------+-------+

Here's the query:

insert into dummy values('Tanzeel', 'SQL Bootcamp', 5/28/2020 12:00 AM, 11/20/2020 12:00 AM); insert into dummy values('John', 'ReactJS for Beginners', 6/28/2020 12:00 AM, 10/20/2020 12:00 AM);

But I'm getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12:00 AM, 11/20/2020 12:00 AM)' at line 1

Clearly something is wrong with Date format, so I checked this link:

Date and Time Conversions Using SQL Server

I tried putting dates in single quotes which the gives this error:

ERROR: 1292 (22007): Incorrect date value: '5/28/2020 12:00 AM' for column 'sdate' at row 1

I've also tried these formats:

enter image description here

Improve this question
7
  • 3
    You are using MySql, not SQL Server. The date values must be enclosed in single quotes and have the format 'YYYY-MM-DD hh:mm:ss' Commented Sep 19, 2020 at 9:00
  • @forpas, Tried single quotes also. I've updated the question. Plz review. Commented Sep 19, 2020 at 9:02
  • 2
    You did not try the correct format. Commented Sep 19, 2020 at 9:02
  • See i've a screenshot also with all the format that i thought would work. Commented Sep 19, 2020 at 9:06
  • 1
    @Tanzeel as Progman and forpas mention you need to insert any date with format YYYY-MM-DD hh:mm:ss. Once inserted correctly, you can use the functions listed in the link you shared to "read" these dates and format that output. Commented Sep 19, 2020 at 9:10

3 Answers 3

Reset to default
1

For MySql the format of your dates is '%m/%d/%Y %h:%i %p'.

So either use str_to_date() to convert them:

insert into dummy values( 'Tanzeel', 'SQL Bootcamp', str_to_date('5/28/2020 12:00 AM', '%m/%d/%Y %h:%i %p'), str_to_date('11/20/2020 12:00 AM', '%m/%d/%Y %h:%i %p') );

Or insert them with the default format:

insert into dummy values( 'Tanzeel', 'SQL Bootcamp', '2020-05-28 00:00:00', '2020-11-20 00:00:00' );
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Tanzeel both statements work. See: dbfiddle.uk/… and dbfiddle.uk/…
0

You shouldn't rely on the default date format. Instead, it's usually a better idea to explicitly state it. E.g.:

INSERT INTO dummy VALUES ('Tanzeel', 'SQL Bootcamp', STR_TO_DATE('2020-05-28 12:00', '%Y-%m-%d %H:%i'), STR_TO_DATE('2020-20-11 12:00', '%Y-%m-%d %H:%i') );
Improve this answer

8 Comments

ERROR: 1411 (HY000): Incorrect datetime value: '2020-05-28 12:00' for function str_to_date
@Tanzeel I buggered up the format, sorry about that. See my edited answer.
@Luuk, No I didn't.
@Tanzeel what output is returned by select date(now()); ?
"still same error" is not very much info. Please re-check if you did not make any typing errors. When you are sure this produces an error, edit you question, and add all the info you have to your question (with examples, and complete error messages copy/pasted!).
|
0

The easiest way is to use a Standard SQL timestamp literal which requires 'YYYY-MM-DD HH:Mi:SS' format

insert into tab values( 'Tanzeel', 'SQL Bootcamp', timestamp '2020-05-28 00:00:00', timestamp '2020-11-20 00:00:00' );

But if you got a DATE column you don't need to insert a timestamp, use a date literal.

insert into tab values( 'Tanzeel', 'SQL Bootcamp', date '2020-05-28', date '2020-11-20' );
Improve this answer

2 Comments

This question is already been answered. But thanks a lot. I appreciate your answer. :-)
Can you please look into this: stackoverflow.com/questions/63967370/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.