CODEWITHSUNDEEP
mysqldatetimeselecttimestamp
Sukanta Paul
Sukanta Paul

Reputation: 792

Modify Date with custom values in MYSQL

I have 2 datetimes in MYSQL table. I need to get a new datetime using part of the dates.

Example: Date 1: 2012-10-23 10:44:21 , Date 2: 2011-06-02 05:40:10

I need Date 3: 2012-10-02 05:40:10 Like this.

Year and Month from Date 1 and the rest from Date 2. How to do this?

Upvotes: 1

Views: 139

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

Build a string using the values you need: 

Select concat(Year(date 1),
Month(date 1),
+ right(date 2, 11) ) as stringDate
;

Then use makedate, str_to_date,date_format functions.

Select str_to_date(stringDate, '%Y-%m-%d %h:%i:%s');

PS: on mobile unable to type entire working code.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT CONCAT(DATE_FORMAT('2012-10-23 10:44:21', '%Y-%m-'), DATE_FORMAT('2011-06-02 05:40:10', '%d %H:%i:%s')) test;

Output is:

test
------------------
2012-10-02 05:40:10

Upvotes: 2

echo_Me
echo_Me

Reputation: 37233

to get year do this

    YEAR(date1)

and the month

     MONTH(date1)

to get the rest from date2

   SUBSTRING('date2', 8 , 11)

Upvotes: 0

Related Questions