CODEWITHSUNDEEP
mysqldate
Sherif
Sherif

Reputation: 359

Updating date in mySql

I have a DATE column with dates in 2012-02-25 format. I need to update it to 12-02-25 but am not sure how to go about it?

Thanks!

Edit: After reviewing all the answers, it seems I have not been clear enough. I do not wish to change my type, nor do I wish to play with the output. I want to update the existing date column from 2012 to 12. It should be an UPDATE query if anything, I'm just not sure how to write it. Thanks for all the comments so far.

Edit 2: It seems my question did not make sense, I was not aware you could not store DATE as xx-xx-xx. Thanks anyhow!

Upvotes: 0

Views: 15403

Answers (5)

Omesh
Omesh

Reputation: 29121

It's not possible to store date in this format '12-02-25' in column having DATE as datatype. You can store it in column having data type as CHAR(8) and then use following UPDATE query:

UPDATE table_name
SET date_column = DATE_FORMAT(date_column, '%y-%m-%d');

but better approach would be to store it in DATE format only and use DATE_FORMAT function while retrieving the data from table.

Upvotes: 4

J.K
J.K

Reputation: 2393

sample code try code

public class MyDateParser {

public static void main(String args[]) throws ParseException {
    String s = "2011-03-27";// or 3/27/2011

    SimpleDateFormat dateFormatter = s.length() == 9 ? new SimpleDateFormat(
            "yyyy-MM-dd") : new SimpleDateFormat("yyyy-MM-dd");
    Calendar calendar = Calendar.getInstance();
    Date date = dateFormatter.parse(s);
    calendar.setTime(date);



    SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yy-MM-dd");

    String strss = simpleDateFormat2.format(date);
    System.out.println(strss);

}

}

Upvotes: 0

Ricardo Marimon
Ricardo Marimon

Reputation: 10697

Use the mysql date_format function to display it as you need:

SELECT DATE_FORMAT(youdatecolumn, '%y-%m-%d') FROM yourtable

Upvotes: 1

Sujay
Sujay

Reputation: 6783

From my MySQL Reference:

Date values with two-digit years are ambiguous because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.

For DATETIME, DATE, and TIMESTAMP types, MySQL interprets dates specified with ambiguous year values using these rules:

Year values in the range 00-69 are converted to 2000-2069.

Year values in the range 70-99 are converted to 1970-1999.

For YEAR, the rules are the same, with this exception: A numeric 00 inserted into YEAR(4) results in 0000 rather than 2000. To specify zero for YEAR(4) and have it be interpreted as 2000, specify it as a string '0' or '00'.

Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the values you require, you must provide unambiguous input containing four-digit year values.

Upvotes: 0

user1329212
user1329212

Reputation:

You should use date-time type. Otherwise you will have some problems (sorting, relating with dates etc.)

You can change display of the date as output.

Upvotes: 0

Related Questions