DATEFORMAT() in MYSQL

Question

I want to insert into table some particular values. However, I can not add date to my table. Other values can be added easily. I used this query

INSERT INTO `student` (`bdate`) VALUES ('30.05.1992');

I need to add in this format. I tried to use DATE_FORMAT('30.05.1992','%d.%m.%y') it also didn't help.

You forgot the quotes around the second argument to `DATE_FORMAT`. — Barmar, Mar 29 '16 at 04:26
Do it the opposit way. Insert `bdate` with accepted format like `1992-05-30` and format the data while reading ;) — zyexal, Mar 29 '16 at 04:26
`date_format` is for date->string, `str_to_date` is for string->date. — Barmar, Mar 29 '16 at 04:32
it doesn't work with both of date_format and str_to_date. When I tried to use str_to_date: I got error like below com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect date value: '30.05.1997' — Soulmaster, Mar 29 '16 at 04:33
Please have a look [here - MySQL date format](http://stackoverflow.com/a/5367816/2675129) — zyexal, Mar 29 '16 at 04:38

mmuzahid · Accepted Answer · 2016-03-29T04:34:46.930

1

INSERT INTO `student` (`bdate`) VALUES (STR_TO_DATE('30.05.1992', '%d.%m.%Y'));

edited Mar 29 '16 at 04:34

answered Mar 29 '16 at 04:20

mmuzahid

2,252
23
42

what is the type of ``bdate`` ? – mmuzahid Mar 29 '16 at 04:26
@Soulmaster Because you had a syntax error, see my comment above. – Barmar Mar 29 '16 at 04:26
@mmuzahid , type of bdate is date – Soulmaster Mar 29 '16 at 04:27
1

`%y` should be `%Y`, because it's a 4-digit year. – Barmar Mar 29 '16 at 04:33
@Soulmaster : As far I know you cant change date field format a DB level. Just change it when you need to display it. – mmuzahid Mar 29 '16 at 04:36
@All [VMais answer](http://stackoverflow.com/questions/25877242/saving-30-feb-on-mysql-date-formating/25878260#25878260) might be interesting for you. – zyexal Mar 29 '16 at 04:44
If you check data in table then it will be yyyy-mm-dd format then what is benefit to insert in this way. – Zafar Malik Mar 29 '16 at 04:46
@ZafarMalik : may be Soulmaster getting the value at this format from some source and storing it as Date – mmuzahid Mar 29 '16 at 04:58
@zyexal thanks for VMais answer, it was interesting :) – mmuzahid Mar 29 '16 at 05:02
@Soulmaster if the answer helps you...you could accept it as answer – mmuzahid Mar 31 '16 at 11:25

score 0 · Answer 2 · answered Mar 29 '16 at 04:21

0

If your field type is date then you can insert data in yyyy-mm-dd format only, even at the time of fetching data you can convert it into your own format.

If you want to insert date in your own format then you can use varchar data type, even at the time of fetching data it will not be optimized and you can get slowness.

answered Mar 29 '16 at 04:21

Zafar Malik

6,734
2
19
30

Is there anyway to put date like 05.05.2005 keeping field type as date? – Soulmaster Mar 29 '16 at 04:26
no. its not possible. http://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html – Thejas Mar 29 '16 at 04:41
2

when you can easily convert date format at the time of fetching data then why are you bother to insert in specific format. – Zafar Malik Mar 29 '16 at 04:47

score 0 · Answer 3 · answered Mar 29 '16 at 04:35

0

It should be

INSERT INTO `student` (`bdate`) VALUES (STR_TO_DATE('30.05.1992', '%d.%m.%Y'));

as year is 4 digit such that 1992,so it should be %Y

answered Mar 29 '16 at 04:35

aroy

452
2
10

DATEFORMAT() in MYSQL

3 Answers3