Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @StartDate DATE = '20110901'
- , @EndDate DATE = '20111001'
- SELECT DATEADD(DAY, nbr - 1, @StartDate)
- FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
- FROM sys.columns c
- ) nbrs
- WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
- WITH Dates AS (
- SELECT
- [Date] = CONVERT(DATETIME,'09/01/2011')
- UNION ALL SELECT
- [Date] = DATEADD(DAY, 1, [Date])
- FROM
- Dates
- WHERE
- Date < '10/10/2011'
- ) SELECT
- [Date]
- FROM
- Dates
- OPTION (MAXRECURSION 45)
- DECLARE @dates TABLE(dt datetime)
- DECLARE @dateFrom datetime
- DECLARE @dateTo datetime
- SET @dateFrom = '2001/01/01'
- SET @dateTo = '2001/01/12'
- WHILE(@dateFrom < @dateTo)
- BEGIN
- SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
- INSERT INTO @dates
- SELECT @dateFrom
- END
- SELECT * FROM @dates
- --Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
- ALTER FUNCTION [dbo].[DateRange]
- (@startDate AS DATE,
- @EndDate AS DATE,
- @interval AS INT
- )
- RETURNS @Dates TABLE(dateValue DATE)
- AS
- BEGIN
- WITH Dates
- AS (
- SELECT [Date] = CONVERT( DATETIME, @startDate)
- UNION ALL
- SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
- FROM Dates
- WHERE Date < @EndDate)
- INSERT INTO @Dates
- SELECT [Date]
- FROM Dates
- OPTION(MAXRECURSION 900);
- RETURN;
- END;
- Declare @StartDate datetime = '2015-01-01'
- Declare @EndDate datetime = '2016-12-01'
- declare @DaysInMonth int
- declare @tempDateRange Table
- (
- DateFrom datetime,
- DateThru datetime
- );
- While @StartDate<=@EndDate
- begin
- SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))
- IF DAY(@StartDate)=1
- SET @EndDate=DATEADD(DAY,14,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
- SET @EndDate=DATEADD(DAY,14,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
- SET @EndDate=DATEADD(DAY,15,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
- SET @EndDate=DATEADD(DAY,12,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
- SET @EndDate=DATEADD(DAY,13,@StartDate)
- INSERT INTO @tempDateRange (DateFrom,DateThru)
- VALUES
- (
- @StartDate,
- @EndDate
- )
- SET @StartDate=DATEADD(DAY,1,@EndDate)
- IF @EndDate< '2016-12-31'
- IF DAY(@StartDate)=1
- SET @EndDate=DATEADD(DAY,14,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
- SET @EndDate=DATEADD(DAY,14,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
- SET @EndDate=DATEADD(DAY,15,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
- SET @EndDate=DATEADD(DAY,12,@StartDate)
- ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
- SET @EndDate=DATEADD(DAY,13,@StartDate)
- end ;
- select * from @tempDateRange
- +++++++++++++++++++++++++++++
- Result:
- DateFrom |DateThru
- select
- dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
- from (
- select row_number() over (order by c.object_id) as nbr from sys.columns c
- ) nbrs
- where
- nbr - 1 <= datediff(
- day,
- convert(date, '2017-01-01'),
- convert(date, '2018-12-31')
- )
- view: date_series {
- derived_table: {
- sql:
- select
- dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
- from (
- select row_number() over (order by c.object_id) as nbr from sys.columns c
- ) nbrs
- where
- nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
- }
- dimension: date {
- primary_key: yes
- type: date
- sql: ${TABLE}.d ;;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement