MySQL Date Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MySQL Date Problem

Author

Topic

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-12-05 : 09:13:27

Hi All,

Please tell me how to insert a date of the format 'dd/mm/yyyy' in MySQL,

for example : 26th December 2006 i.e. - '26/12/2006' in Indian date format.

I want it to store it in that format.....and all the processes that i do should be, by default, in that format only.

Thank You..
:)

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:15:59

No, you really do not want to store dates as strings. Use proper date types.

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-12-05 : 09:26:51

It is defined as date type....

but while inserting the following..i get the following error

mysql> insert into bills (bnumber, account, vendor, bdate) values ('001', 'ashpi
n', 'A', '26/12/2006');
ERROR 1292 (22007): Incorrect date value: '26/12/2006' for column 'bdate' at row
1

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:29:47

Either use ISO date format 'YYYYMMDD', or 'YYYY-MM-DD'.
Or find a way to alter the internal DATEFORMAT, as you do in MS SQL Server with SET DATEFORMAT {mdy | dmy }

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-12-05 : 09:32:50

can u please tell me how....???

i tried it this way

mysql> insert into bills (bnumber, account, vendor, bdate) values ('001', 'ashpin', 'A', date_format(26/12/2006, '%d/%m/%Y'));
ERROR 1292 (22007): Truncated incorrect datetime value: '0.00108009'

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:33:55

insert into bills (bnumber, account, vendor, bdate) values ('001', 'ashpin', 'A', '20061226');
insert into bills (bnumber, account, vendor, bdate) values ('001', 'ashpin', 'A', '2006-12-26');

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-12-05 : 09:34:52

but it is not in dd/mm/yyyy format

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:36:00

No, it is not!
But did it work?

And why is it SO important to insert the dates in that particular format?

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-12-05 : 09:40:03

because the user is entering it in that format.......and thats the users need....
also i tried it..but it is inserting 0000-00-00
as shown below

mysql> insert into bills (bnumber, account, vendor, bdate) values ('001', 'ashpi
n', 'A', 26/12/2006);
Query OK, 1 row affected (0.03 sec)

mysql> select * from bills;
+----+---------+------------+--------+---------+
| id | bnumber | bdate | vendor | account |
+----+---------+------------+--------+---------+
| 2 | 001 | 0000-00-00 | A | ashpin |
+----+---------+------------+--------+---------+
1 row in set (0.00 sec)

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:44:01

Well... Where do I start?
1) Where are the users entering the values? If on a web page, reformat it on the web page BEFORE inserting to database.

2) Why do you continue to insert values in dmy format when I specifically asked you to do it in ISO-format? Try to insert the date again with ISO format this time and report back if it works.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 10:20:59

1 Post at Mysql forum www.MYSQL.com
2 Always use date or datetime parameter in the front end
3 Dont worry on how Dates are stored in a table. It is front end, where you should format as dd/mm/yyyy
4

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources