Create Date from Day, Month, Year Fields in MySQL

Create date from day, month, year fields in MySQL

You can use STR_TO_DATE() function.

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

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.

MySQL date columns in day, month year

You can do:

SELECT *
FROM tbl a
INNER JOIN tbl b 
    ON UNIX_TIMESTAMP(CAST(CONCAT(a.year, '-', a.month, '-', a.day) AS DATETIME)) = b.unixtimecol

But the unix timestamp column in tableB is more precise to the second, whereas the columns in your tableA can only be precise to the day. As a result, it may not always join when you want it to.

If you want it to join where the two times are within the same day, you can also make the unix time column a DATE type so that both are only precise to the day:

SELECT *
FROM tbl a
INNER JOIN tbl b 
    ON CAST(CONCAT(a.year, '-', a.month, '-', a.day) AS DATE) = DATE(FROM_UNIXTIME(b.unixtimecol))

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

How to get full date from month and year from first two columns and update the third column with the full date

You can use the following, using STR_TO_DATE with CONCAT (or CONCAT_WS):

-- solution using STR_TO_DATE with CONCAT
UPDATE table_name 
  SET `date` = STR_TO_DATE(CONCAT(`year`, '-', `month`, '-1'), '%Y-%c-%e')
WHERE `date` IS NULL

-- solution using STR_TO_DATE with CONCAT_WS
UPDATE table_name 
  SET `date` = STR_TO_DATE(CONCAT_WS('-', `year`, `month`, 1), '%Y-%c-%e')
WHERE `date` IS NULL

You can also create a VIEW to get the last column (date) dynamically without updating your table:

CREATE VIEW view_name AS
  SELECT `id`, `month`, `year`, STR_TO_DATE(CONCAT_WS('-', `year`, `month`, 1), '%Y-%c-%e') AS `date`
  FROM table_name

demo on dbfiddle.uk

MySql: Create datetime from year and month

You can create the date as a number and then convert it to a date:

where myDateTime >= date(@year * 10000 + @month * 100 + 1) and
      myDateTime < date_add(date(@year * 10000 + @month * 100 + 1), interval 1 month)

MySQL: How to do date between by year and month (no date)

If you want to take all rows from 2010-11 to 2011-07, until the first day of August:

SELECT * FROM `table` 
WHERE `date_column` BETWEEN '2010-11-01' AND '2011-08-01'

Use this query if you want to get all rows from the full months of January to June:

SELECT * FROM `table`
WHERE YEAR(`date_column`)=2011 AND MONTH(`date_column`) BETWEEN 1 AND 6

If you want to use different years, then write different queries for each year:

SELECT * FROM `table` 
WHERE 
  (YEAR(`date_column`)=2010 AND MONTH(`date_column`) BETWEEN 11 AND 12) OR 
  (YEAR(`date_column`)=2011 AND MONTH(`date_column`) BETWEEN 1 AND 7)

---

Related Topics