CODEWITHSUNDEEP
mysqldate
chongman
chongman

Reputation: 2507

MYSQL: How do I set a date (makedate?) with month, day, and year

I have three columns, y, m, and d (year, month, and day) and want to store this as a date.

What function would I use on mySQL to do this?

Apparently makedate uses year and day of year (see below), but I have month.

I know I can use STR_TO_DATE(str,format), by constructing the string from (y,m,d), but I would guess there is an easier way to do it.

REFERENCES

MAKEDATE(year,dayofyear)

Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL.

Upvotes: 8

Views: 28752

Answers (3)

Nikesh K
Nikesh K

Reputation: 621

i hope this wil work out..

str_to_date( concat( year( curdate( ) ) , '-', month( a.dob ) , '-', day( a.dob ) ) , '%Y-%m-%d' )

Where a.dob is my column's name which has DATE

Upvotes: 2

josh
josh

Reputation: 51

It isn't high on readability but the following would work:

SELECT'1900-01-01' + INTERVAL y-1900 YEAR + INTERVAL m-1 MONTH + INTERVAL d-1
DAY FROM ...

Not sure that this any more efficient than using CONCAT and STR_TO_DATE.

Upvotes: 5

Kaleb Brasee
Kaleb Brasee

Reputation: 51955

I believe you can use a string in the proper format:

UPDATE table SET my_date = '2009-12-31';

Edit: Yeah you can, just verified it in MySQL 5.1.

Upvotes: 12

Related Questions