How do I separate a mysql datestamp field into year, quarter, month and day

Question

I have a mysql database with a datestamp field for entries. I'd really like to separate out the year, quarter, month and day into individual columns. I can make the new columns without a problem but I don't know how to write a query that does this easily. I've been playing around with things like this

SELECT MONTH();
FROM `datestamp`

But haven't had any joy...

Any help gratefully received :-)

is your table called datestamp, or is that a column in your table? — kinakuta, Jun 17 '11 at 09:07

score 0 · Answer 1 · answered Jun 17 '11 at 09:06

0

This should get you started...

SELECT DAY(`datestamp`) AS `day`,
       MONTH(`datestamp`) AS `month`,
       YEAR(`datestamp`) AS `year`,
       QUARTER(`datestamp`) AS `quarter`
  FROM `table`;

answered Jun 17 '11 at 09:06

alex

479,566
201
878
984

Thanks - this is what did it... 'UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Year` = YEAR(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Quarter` = QUARTER(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Month` = MONTH(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Date` = DAYOFMONTH(datestamp);' – Bruce Jun 19 '11 at 11:46

score 0 · Answer 2 · answered Jun 17 '11 at 09:11

0

This should work:

SELECT MONTH(`datestamp`) AS `datemonth`, 
DAY(`datestamp`) AS `dateday`,
YEAR(`datestamp`) AS `dateyear`,
QUARTER(`datestamp`) AS `datequarter`
FROM `table`;

answered Jun 17 '11 at 09:11

Abhay

6,545
2
22
17

Thank you,this is what did it... 'UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Year` = YEAR(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Quarter` = QUARTER(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Month` = MONTH(datestamp); UPDATE `fourg124_lime2`.`lime_survey_81823` SET `Date` = DAYOFMONTH(datestamp);' – Bruce Jun 19 '11 at 11:47

How do I separate a mysql datestamp field into year, quarter, month and day

2 Answers2