Menu
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.
905
Introduction to MySQL DATE data type This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want. MySQL uses 3 bytes to store a DATE value.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
$todaysDate="2012-26-11"; $db = Zend_Registry::get("db"); $result = $db->fetchAll("SELECT * FROM `table` WHERE active=0 AND {$todaysDate} between dateStart and dateEnd"); return $result; But so far it's not working as it returns zero rows. J.K.A.
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.
92
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.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
