mysql date problem

[AmiMitra]

i am using asp.net......i have a textbox where i am inserting a date in the format 'dd/mm/yyyy' . i have to insert this data into database. but mysql date format is 'yyyy-mm-dd'...how to do that? please help...

[prabirchoudhury]

hey.. here you go ..

i have done this conversion on C# ,

 
string str = TextBox1.Text; //put the value you pull from textbox
 
        string[] strArr = str.Split('/');
        string strDD = strArr[0];
 
        string strMM = strArr[1];
        string strYYYY = strArr[2];
 
        string str2 = strYYYY + "-" + strMM + "-" + strDD;
        Message.Text = str2; 
 
 
// str2 is yyyy/mm/dd format 
 
 
 
 
 

you could set up a date picker popup , to pick the date

[r035198x]

Use the proper API that allows you to pass the values as parameters and therefore will do the conversion automatically for you.

[mikek12004]

yes but how can you pass parameter say to the now() function to store date/tome in dd/mm/yy hh:mm:ss and not in the default way mysql does?

[r035198x]

Don't change the default way that MySQL stores the dates. Just format the dates according to the relevant Locales when you retrieve them for presentation.

[mikek12004]

doesn't mysql give you that possibility? in mssql the date function gave me much flexibility regarding that matter (I change from mssql to mysql and I want to make as little changes to the php code as possible)

[r035198x]

The date function in mssql does not change the way mssql server stores it's dates in the database. It merely changes between varchars and dates.
Like I said, you never need to change the way dates are stored in the database. don't do it and don't look for ways of doing it. People use different date formats on their computers. No need to try and mirror all those formats in the database.
Use the default database date format at the back end and use appropriate API at the front end that does the conversions automatically for you.

[mikek12004]

hmmm...maybe I wasn't that clear,I do not care what inner processes mysql do or how its saving date, all I care is that when I draw the date to display it I do not want the american (year-month-day) but the western way (day-month-year) and thought instead of doing it in php after I have drawn the date if there was a way to store it in that format like I did in mssql (I believe it was with the 103 parameter) if on the other hand mysql doesn't give such capabilities...oh well I guess php is the next best way to go

[r035198x]

Yes, MySQL does have date format functions.
See the mysql refmanual here.

[AmiMitra]

thanx for your concern , but please can u help me once more?
when i create the table in mysql where i want to insert that date, should i keep it as varchar or date? because any format i get is in the form .text , which is a string. is there any way to convert string to date?

[AmiMitra]

ok i solved it. if i use the convert.datetime function , it again changes the format to dd/mm/yy format. so i need not do that. all i have to do is simply insert the changed format text as string and it is done. it is then inserted as 'yyyy-mm-dd' in the database.am i right?

[r035198x]

It is inserted in the default format. Once again you shouldn't have to worry about that default format at all. You do have to worry about how you present those dates to users and that is where the Date_Format and Convert_TZ functions come into it.

[prabirchoudhury]

make the data Type in mysql as DATE or DATETIME for the Date field

Mysql field type