Clevere Datums- und Zeitfunktionen

Veröffentlicht von Autor am

— current date
SELECT CURRENT_DATE AS date_current_date;

— start of last week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY), INTERVAL -1 WEEK) AS start_of_last_week;

— end of last week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY), INTERVAL -1 WEEK) AS end_of_last_week;

— start of current week (begin: monday)
SELECT DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY) AS start_of_current_week;

— end of current week (begin: monday)
SELECT DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY) AS end_of_current_week;

— start of next week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY), INTERVAL 1 WEEK) AS start_of_next_week;

— end of next week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY), INTERVAL 1 WEEK) AS end_of_next_week;

— start of last month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-2 MONTH) AS start_of_last_month;

— end of last month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-1 MONTH), INTERVAL -1 DAY) AS end_of_last_month;

— start of current month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-1 MONTH) AS start_of_current_month;

— end of current month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH), INTERVAL -1 DAY) AS end_of_current_month;

— start of next month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH) AS start_of_next_month;

— end of next month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)+1 MONTH), INTERVAL -1 DAY) AS end_of_next_month;

— start of last year
SELECT MAKEDATE(YEAR(CURRENT_DATE)-1, 1) AS start_of_last_year;

— end of last year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL -1 DAY) AS end_of_last_year;

— start of current year
SELECT MAKEDATE(YEAR(CURRENT_DATE), 1) AS start_of_current_year;

— end of current year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE)+1, 1), INTERVAL -1 DAY) AS end_of_current_year;

— start of next year
SELECT MAKEDATE(YEAR(CURRENT_DATE)+1, 1) AS start_of_next_year;

— end of next year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE)+2, 1), INTERVAL -1 DAY) AS end_of_next_year;

Kategorien: ITSQL

Ähnliche Beiträge

SQL

Date_format

SELECT DATE_FORMAT(„2017-06-15“, „%m %d %Y“); Format Description %a Abbreviated weekday name (Sun to Sat) %b Abbreviated month name (Jan to Dec) %c Numeric month name (0 to 12) %D Day of month, numeric, follwed by Weiterlesen

SQL

DROP primary key

ALTER TABLE tab DROP PRIMARY KEY; funktioniert leider nicht Tabelle umbenennen, Daten aus alte in neue kopieren, im neuen Tabelle Primary key löschen

SQL

Select id automatisch hoch setzen

SET @x =0;SELECT (@x:=@x+1), a, b, c FROM tab; ———- Beispiel:drop table p_music_auf_LP_mit_id ; set @x =0;create table p_music_auf_LP_mit_idSELECT(@x:=@x +1) AS id, year, interpret_lastname, interpret_prename, albumFROM p_music_auf_LP;