Change the date format to dd/mm/yyyy in mysql

ajos777 · October 25, 2007, 6:59am

hi friends,
Ive a small problem(so to speek,infact its been buggin me for 2 days).I am using java and phpmyadmin(wamp).I just migrated my database from access to mysql and came to know the format of mysql date is of type datetime and the time format is yyyy-mm-dd.Iwant to change it to dd/mm/yyyy.I have checked the mysql documentation but coudnt really get the point.I have decided to change the format in my query(is wat i read in a post) which is advisable.My query goes this way–>

INSERT INTO Internal_Publicity_Details (Branch,Publicity_Nature,Brief_Part1,Brief_Part2,Brief_Part3,Brief_Part4,Specifics_1,Specifics_2,Specifics_3,Specifics_4,Publicity_Media,Publicity_Period,Market_Coverage,Proposed_By,[B]Req_Date[/B],Remarks,Id_No) VALUES

i am entering the date values from my jsp form.Here Req_Date(bold) is my date column.the date which the user will enter is random ie.for example
20/11/2006 or 20/12/2004 like this.please help me regarding this as m completely new to database.I even tried something like this which went horribly wrong –>

INSERT INTO Internal_Publicity_Details (Branch,Publicity_Nature,Brief_Part1,Brief_Part2,Brief_Part3,Brief_Part4,Specifics_1,Specifics_2,Specifics_3,Specifics_4,Publicity_Media,Publicity_Period,Market_Coverage,Proposed_By,Req_Date,Date_Format(`Req_Date`,'%d/%m/%Y'),Remarks,Id_No) VALUES

thanks.
regards,
ajos777

leonglass · October 25, 2007, 7:30am

I did something like this recently and as far as I can remember all I did was format the way I wanted it at the time it was being output to the page. You can quite happily leave the formatting in the db and change it when you output the data. I can’t remember how I did that at the moment but will post back later when I get home.

ajos777 · October 25, 2007, 7:38am

heya leonglass,
Thanks for the reply.My jsp form is very simple,i.e i just want to enter the values which is in my INSERT query.at the moment im not displaying my inserted values.Your inputs will be highly appreciated.
thanks,
ajos777

r937 · October 25, 2007, 9:22am

you must ensure that the value passed to mysql is in year-month-day sequence

for example –

‘2007-10-25’
‘2007.10.25’
‘2007/10/25’
‘2007$10$25’
‘20071025’
20071025

ajos777 · October 25, 2007, 9:31am

thanks for the reply.
Ok let me put it this way.when i enter the values in the form in yyyy-mm-dd format i got an error.Here is the error–>
MYSQLException
SQLState:23000
Message:Duplicate entry ‘0’ for key 1
Error:1062
what does this mean? and for the way i want to enter in my db is dd/mm/yyyy.how is it possible?
regards,
ajos777

r937 · October 25, 2007, 9:43am

that error message says you are trying to insert a row with a duplicate value for the first key in the table

you cannot enter your dates in dd/mm/yyyy format – you must enter your dates in year-month-day sequence

felgall · October 25, 2007, 9:47am

You can retrieve dates in whatever format you want

SELECT DATE_FORMAT(date_field, ‘%d/%m/%Y’)

You must however save all dates using the Y-m-d format as that is the only format that date fields can accept (it is also the only format where you can sort into date order)…

ajos777 · October 25, 2007, 9:48am

the error that i mentioned above was when i entered the value of date in the format yyyy-mm-dd, so isnt this suppossed to be the ‘right’ way?
kind regards
ajos777

ajos777 · October 25, 2007, 9:53am

Hi felgall,
Thanks for your reply.So are you saying that i cannot enter the date in my jsp form in any other format except yyyy-mm-dd?
what im trying to do is,im inserting certain form fields in my database.(im not trying to retrieve them)
kind regards,
ajos777

r937 · October 25, 2007, 10:09am

the acceptable formats allow for any character between yyyy and mm and dd, or none

but the year has to come first, then the month, then the day

r937 · October 25, 2007, 10:09am

yes, that will work – but apparently you have a unique index on that column

ajos777 · October 25, 2007, 10:17am

yes indeed i have a unique index for that column.im using phpmyadmin(wamp) and i was playing around the action keys(which has the edit,delete,primary…) and accidently clicked on the index option.how do i change the index in to something like ‘not-so-unique’(so to speak!).
thank you.
kind regards,
ajos777

r937 · October 25, 2007, 10:39am

sorry, no idea

have you tried un-clicking on whatever it is you clicked on?

ajos777 · October 25, 2007, 10:44am

yes,what ive done is delete the Req_Date colum from the table and again created it.And i tried again but the same error message came up.How will i come to know at which point the error is pointing towards?
i.e ive the error message–>Duplicate entry ‘0’ for key 1
and my query is–>

INSERT INTO Internal_Publicity_Details (Branch,Publicity_Nature,Brief_Part1,Brief_Part2,Brief_Part3,Brief_Part4,Specifics_1,Specifics_2,Specifics_3,Specifics_4,Publicity_Media,Publicity_Period,Market_Coverage,Proposed_By,Req_Date,Remarks,Id_No) VALUES

where possibly it may be pointing to?
regards,
ajos777

r937 · October 25, 2007, 10:46am

please do a SHOW CREATE TABLE for your table

and show the entire query please

ajos777 · October 25, 2007, 11:19am

ok…i did the show create table for internal_publiclity_details and got the result something like this–>
Table–> internal_publicity_details
Create Table —>


 CREATE TABLE `internal_publicity_details` (\
  `Id_No` int(11) NOT NULL,\
  `Req_Date` datetime default NULL,\
  `Branch` varchar(50) default NULL,\
  `Publicity_Nature` varchar(255) default NULL,\
  `Publicity_Media` varchar(255) default NULL,\
  `Publicity_Period` varchar(50) default NULL,\
  `Market_Coverage` varchar(50) default NULL,\
  `Proposed_By` varchar(50) default NULL,\
  `Brief_Part1` varchar(255) default NULL,\
  `Brief_Part2` varchar(255) default NULL,\
  `Brief_Part3` varchar(255) default NULL,\
  `Brief_Part4` varchar(255) default NULL,\
  `Specifics_1` varchar(255) default NULL,\
  `Specifics_2` varchar(255) default NULL,\
  `Specifics_3` varchar(255) default NULL,\
  `Specifics_4` varchar(255) default NULL,\
  `Remarks` varchar(255) default NULL,\
  PRIMARY KEY  (`Id_No`),\
  KEY `Id_No` (`Id_No`)\
) ENGINE=InnoDB DEFAULT CHARSET=latin1

regards,
ajos777

r937 · October 25, 2007, 12:18pm

okay, that shows that you have only the PRIMARY KEY that is unique

(note that the extra KEY on Id_No is redundant – you don’t need it)

this means your duplicate error is coming from the Id_No

could you show the entire INSERT query please?

ajos777 · October 25, 2007, 12:22pm

thanks for the reply,
my query is—>

INSERT INTO Internal_Publicity_Details (Branch,Publicity_Nature,Brief_Part1,Brief_Part2,Brief_Part3,Brief_Part4,Specifics_1,Specifics_2,Specifics_3,Specifics_4,Publicity_Media,Publicity_Period,Market_Coverage,Proposed_By,Req_Date,Remarks,Id_No) VALUES

regards,
ajos777

r937 · October 25, 2007, 12:26pm

okay, that’s the same one you posted earlier, and it is incomplete

i.e. there are no values!!

ajos777 · October 25, 2007, 12:34pm

ok when i insert the values in the table without the id_no i get something like this—>


INSERT INTO `publicity_db`.`internal_publicity_details` (

`Id_No` ,
`Req_Date` ,
`Branch` ,
`Publicity_Nature` ,
`Publicity_Media` ,
`Publicity_Period` ,
`Market_Coverage` ,
`Proposed_By` ,
`Brief_Part1` ,
`Brief_Part2` ,
`Brief_Part3` ,
`Brief_Part4` ,
`Specifics_1` ,
`Specifics_2` ,
`Specifics_3` ,
`Specifics_4` ,
`Remarks`
)
VALUES (
'', '2007-10-25 17:58:27', 'Bombay', 'outdoor', 'lksjdfj', '3', 'fgkljdf', 'jjhj', 'dfgf', 'dfgf', 'fgfhf', 'fgh', 'fghfgh', 'fghfg', 'hfgh', 'fghfgh', 'ghjghj'
)

MySQL said:

#1062 - Duplicate entry '0' for key 1

in my jsp page i dont have a field for the id_no.Its supposed to increment by itself(is what i suppose).but when i insert the id_no column then the insert operation is successful.Whats the possible solution for this?
regards,
ajos777