Microsoft SQL Server – Datum abfragen

In Microsoft SQL (evtl. auch in anderen SQL-Sprachen) lassen sich mit den folgenden SQL Select Statements verschiedene Zeitpunkte abfragen:

Aktuelles Datum
(current date)

SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS date_current_date

Anfang der letzten Woche (Montag als Start-Tag)
(start of last week (begin: monday))

SELECT CAST(DATEADD(WEEK, -1 ,DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP)) AS DATE) AS start_of_last_week

Ende der letzten Woche (Montag als Start-Tag)
(end of last week (begin: monday))

SELECT CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP)) AS DATE) AS end_of_last_week

Anfang der aktuellen Woche (Montag als Start-Tag)
(start of current week (begin: monday))

SELECT CAST(DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP) AS DATE) AS start_of_current_week

Ende der aktuellen Woche (Montag als Start-Tag)
(end of current week (begins monday))

SELECT CAST(DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP) AS DATE) AS end_of_current_week

Anfang der nächsten Woche (Montag als Start-Tag)
(start of next week (begin: monday))

SELECT CAST(DATEADD(WEEK, 1 ,DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP)) AS DATE) AS start_of_next_week

Ende der nächsten Woche (Montag als Start-Tag)
(end of next week (begin: monday))

SELECT CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP)) AS DATE) AS end_of_next_week

Anfang des letzten Monats
(start of last month)

SELECT CAST(DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1)) AS DATE) AS start_of_last_month

dateadd(m, -1, dateadd(d, (-1) * (datepart(d, getdate())-1), getdate()))

Ende des letzten Monats
(end of last month)

SELECT CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1)) AS DATE) AS end_of_last_month

dateadd(d, -1, dateadd(m, 0, dateadd(d, (-1) * (datepart(d, getdate())-1), getdate())))

Anfang des aktuellen Monats
(start of current month)

SELECT DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1) AS start_of_current_month

Ende des aktuellen Monats
(end of current month)

SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) AS end_of_current_month

Anfang des nächsten Monats
(start of next month)

SELECT DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP), 1)) AS start_of_next_month

dateadd(m, -1, dateadd(d, (-1) * (datepart(d, getdate())-1), getdate()))

Ende des nächsten Monats
(end of next month)

SELECT DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP), 1))) AS end_of_next_month

Anfang des letzten Jahres
(start of last year)

SELECT DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS start_of_last_year

Ende des letzten Jahres
(end of last year)

SELECT DATEADD(DAY, -1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS end_of_last_year

Anfang des aktuellen Jahres
(start of current year)

SELECT DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1) AS start_of_current_year

Ende des aktuellen Jahres
(end of current year)

SELECT DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1))) AS start_of_current_year

Anfang des nächsten Jahres
(start of next year)

SELECT DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS start_of_next_year

Ende des nächsten Jahres
(end of next year)

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, -1)
AS end_of_next_year