Можно ли получить month names
между двумя датами в SQl
т.е.
2011-05-01
И 2011-08-01
- входы
Я просто хочу, чтобы результат был
------------
Month
------------
May
June
July
August
Если какой-либо орган знает запрос, поделитесь им.
Можно ли получить month names
между двумя датами в SQl
т.е.
2011-05-01
И 2011-08-01
- входы
Я просто хочу, чтобы результат был
------------
Month
------------
May
June
July
August
Если какой-либо орган знает запрос, поделитесь им.
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
Результаты:
MonthName
------------------------------
May
June
July
August
(4 row(s) affected)
Вы можете сделать это с помощью рекурсивного CTE, создав таблицу дат и получая имя месяца от каждого:
declare @start DATE = '2011-05-01'
declare @end DATE = '2011-08-01'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<[email protected]
)
select Datename(month,date) from months
Я изменил ответ Jamiec, чтобы вывести также последний день месяца.
declare @start DATE = '2014-05-01'
declare @end DATE = getdate()
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month, 1, date)
from months
where DATEADD(month, 1, date) < @end
)
select [MonthName] = DATENAME(mm, date),
[MonthNumber] = DATEPART(mm, date),
[LastDayOfMonth] = DATEPART(dd, EOMONTH(date)),
[MonthYear] = DATEPART(yy, date)
from months
Что дает вывод:
MonthName MonthNumber LastDayOfMonth MonthYear
May 5 31 2014
June 6 30 2014
July 7 31 2014
August 8 31 2014
September 9 30 2014
Вдохновленный Jamiec answer, но проблема с исправлением с day
больше, чем на day
:
declare @start DATE
declare @end DATE
SELECT @start='2011-05-19' , @end='2011-08-15'
;with months (date)
AS
(
SELECT DATEADD(DAY,1,EOMONTH(@start,-1))
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date) < EOMONTH(@end)
)
select Datename(month,date)
from months
declare @start DATE = '2011-05-30'
declare @end DATE = '2011-06-10'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@end)+1,0))
)
select Datename(month,date) from months
Ну,
@bogdhan sahlean дал хорошее решение, основанное на множестве, но ограничивает значения до 2048 с учетом типа данных date
и datetime2
, который диапазон от года 0001-01-01
до 9999-12-31
, From MSDN
Диапазон дат 0001-01-01 - 9999-12-31
Январь 1,1 CE до 31 декабря 9999 года CE
хотя это крайний случай, но стоит знать. Так как, если в один прекрасный день кто-то пытается проецировать месяцы более 170 лет:)
Даже самые передовые ответы не выполняют некоторые краевые случаи (когда дата началa > дата окончания не покажет дату окончания месяца, также рекурсивный запрос завершается после 100 исполнений по умолчанию). А также использование рекурсивного cte для итерации, которая является производительностью hog при массовом использовании.
Теперь лучшим решением (IMHO) является использование таблицы календаря или таблицы таллинга для создания месяцев между двумя датами. ЕСЛИ нельзя создать таблицу, есть более эффективная альтернатива использованию Itzik ben Gans каскадирует CTE для создания таблицы чисел. (здесь) Что быстрее, Нет логических, физических чтений, Нет рабочей таблицы NADA
Вот код
DECLARE @start DATETIME2 = '00010101'
DECLARE @end DATETIME2 = '99991231'
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT 0 UNION SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT DATENAME(YEAR,DATEADD(MONTH,N,@start)) AS [Year Part], DATENAME(MONTH,DATEADD(MONTH,n,@start)) AS [Month Part]
FROM Tally where N between 0 and DATEDIFF(mm,@start,@end)
ORDER BY n;
NB: я добавил SELECT 0
для запуска чисел из 0-й позиции
Производительность, показанная на моем ПК,
Метод Итцика
(затронуто 119988 строк)
Время выполнения SQL Server: время процессора = 187 мс, прошедшее время = 706 Миз. Время и время компиляции SQL Server: время процессора = 0 мс, прошедшее время = 0 мс.
Одно из рекурсивных решений, приведенных здесь, которое занимает время
(затронуто 119988 строк) Таблица "Рабочий стол". Количество сканирования 2, логическое читает 719923, физический читает 0, чтение вперед читает 0, логические логические данные 0, физическое чтение lob 0, чтение с чтением lob 0.
Время выполнения SQL Server: время процессора = 890 мс, прошедшее время = 1069 мс.
Производительность между таблицей таблеток, таблицей календаря и таблицей чисел itzik может немного отличаться, но работает как шарм со всем диапазоном дат, который вы предоставляете.
Функция создания базы данных, как показано ниже
CREATE FUNCTION [dbo].[DateRange]
(@Identifier CHAR(1),@StartDate DATETIME,@EndDate DATETIME)
RETURNS @SelectedRange TABLE(Dates DATE) AS
BEGIN
;WITH cteRange (DateRange) AS (
SELECT @StartDate
UNION ALL
SELECT
CASE
WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, 1, DateRange)
WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, 1, DateRange)
WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, 1, DateRange)
WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, 1, DateRange)
WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, 1, DateRange)
END
FROM cteRange
WHERE DateRange <=
CASE
WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, -1, @EndDate)
WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, -1, @EndDate)
WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, -1, @EndDate)
WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, -1, @EndDate)
WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, -1, @EndDate)
END)
INSERT INTO @SelectedRange (Dates) SELECT DateRange FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
Затем с помощью функции мы можем сгенерировать диапазон дат
SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')
Если нам нужен форматированный вывод, мы можем сохранить результат в табличной переменной, как показано ниже:
DECLARE @tblDateRange TABLE (AutoID INT IDENTITY(1,1),DateRange DATE)
INSERT INTO @tblDateRange SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')
SELECT
LEFT(DATENAME(MONTH,DateRange),3) [MonthYearValue],YEAR(DateRange) AS [Year]
FROM @tblDateRange
Исходя из наших потребностей, мы можем изменить
OPTION (MAXRECURSION 3660)
Если у вас есть таблица MonthNames
, содержащая имена каждого месяца, вы можете запустить
SELECT MonthName FROM MonthNames WHERE MonthNumber BETWEEN Month(&date1) AND Month(&date2);
Таблица MonthNames будет выглядеть как
MonthName, MonthNumber
1 января
2 февраля
3 марта
и т.д.
Попробуйте следующее:
declare
@sd date=getdate(),
@ld date='2016-01-01'
select
Datename(month,dateadd(month,number,GETDATE())),
number
from master.dbo.spt_values
where type='p'
and dateadd(month,number,GETDATE()) <= @ld