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

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

ajos777 :smiley:

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.

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 the moment im not displaying my inserted values.Your inputs will be highly appreciated.

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

for example –


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–>
Message:Duplicate entry ‘0’ for key 1
what does this mean? and for the way i want to enter in my db is dd/mm/ is it possible?

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

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)…

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

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,

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

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

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

sorry, no idea

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

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?

please do a SHOW CREATE TABLE for your table

and show the entire query please

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`)\


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?

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 


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

i.e. there are no values!!

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` ,
'', '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?