thesuhu

MySQL Generate Tanggal

Apr 16th, 2021 (edited)
203
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* generate tanggal bulan ini */
  2. SELECT tanggal
  3.     FROM
  4.     (
  5.         SELECT MAKEDATE(YEAR(NOW()),1) +
  6.         INTERVAL (MONTH(NOW())-1) MONTH +
  7.         INTERVAL daynum DAY tanggal
  8.         FROM
  9.         (
  10.             SELECT t*10+u daynum FROM
  11.             (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
  12.             (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
  13.             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
  14.             UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
  15.         ) AA
  16.     ) AA WHERE MONTH(tanggal) = MONTH(NOW());
  17.  
  18. /* generate tanggal bulan dan tahun tertentu, parameter $bulan dan $tahun */
  19. SELECT tanggal
  20.     FROM
  21.     (
  22.         SELECT MAKEDATE($tahun,1) +
  23.         INTERVAL ($bulan-1) MONTH +
  24.         INTERVAL daynum DAY tanggal
  25.         FROM
  26.         (
  27.             SELECT t*10+u daynum FROM
  28.             (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
  29.             (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
  30.             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
  31.             UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
  32.         ) AA
  33.     ) AA WHERE MONTH(tanggal) = $bulan;
  34.  
  35. /* mysql 8 only */
  36. SELECT DATE(CONCAT('$tahun-$bulan-',DAY)) tanggal FROM
  37. (
  38. WITH RECURSIVE `days` AS ( SELECT 1 AS `day` UNION ALL SELECT `day` + 1 FROM `days` WHERE `day` < DAY(LAST_DAY(DATE('$tahun-$bulan-1'))) ) SELECT * FROM `days`
  39. ) a ;
  40.  
  41.  
RAW Paste Data