Date and time conversion query in SQL-CodePudding

In my database table, there is a date column i.e. EXPECTED DATE which is in dd-mm-yyyy format, and the datatype of the column is text. Now, I want to convert the format to yyyy-mm-dd. But the date is not changing at all and also when I tried to get the timestamp for the expected date column . I am getting some errors. For date coming I have used this STR_TO_DATE. But the year is not coming like what I expect and the timestamp also.

For example:

select STR_TO_DATE ('30-11-2011', '%d,%m,%y') as date ;

returns a result as

2020-11-30

And for timestamp

select STR_TO_DATE ('2011,11,30 12,30,45', '%y,%m,%d, %H,%I,%S');

I am not getting errors.

Please help me get the correct answers for this problem.

CodePudding user response：

for the first query you need to use the %Y. Remember that it is always better to use "Y" for the years. when you are writing a query on year.

SELECT STR_TO_DATE("30,11,2011", "%d,%m,%Y");

for the second one also you can use '%Y' in the place of '%y' and for minutes use '%i' not '%I' and also for hours and minutes. you can use whatever you like.

SELECT STR_TO_DATE("2011,11,30 12,30,45", "%Y,%m,%d %h,%i,%s");

Refer to the below documentation for more clarification on SQL commands.

CodePudding user response：

You need %Y (capital Y) for the 4 digit year, when using MySQL's STR_TO_DATE. Also, minutes is represented by %i, not %I, the latter which is hours on a 0 to 12 scale. So use:

SELECT STR_TO_DATE('30-11-2011', '%d-%m-%Y');
SELECT STR_TO_DATE('2011,11,30 12,30,45', '%Y,%m,%d %H,%i,%S');