CODEWITHSUNDEEP
sqlsql-serverdatetimerange
Alex Alekseev
Alex Alekseev

Reputation: 23

Datetime values generation MS SQL

There are two variables of the DateTime values available: for example, @STARTDATETIME = '2020-10-21 14:45' and @ENDDATETIME = '2020-10-22 19:00'

If there is only a single day between dates like STARTDATETIME = '2020-10-21 12:00' and ENDDATETIME = '2020-10-21 16:00' then the variables must save the initial values.

If there are one or more days between the values then the first date must start with the given timestamp to 16:00. The all in-between days time must be as from 08:00 till 16:00. And the last day must start with 08:00 time till the given timestamp.

Full example:

@STARTDATETIME = '2020-10-21 14:45' and @ENDDATETIME = '2020-10-23 19:15'

Desired output (table):

STARTDATETIME      | ENDDATETIME 
'2020-10-21 14:45' | '2020-10-21 16:00'
'2020-10-22 08:00' | '2020-10-22 16:00'
'2020-10-23 08:00' | '2020-10-23 19:15'

Upvotes: 2

Views: 66

Answers (2)

ub_coding
ub_coding

Reputation: 139

This one will give you one row per day:


    DECLARE @start dateTime = '20201021 14:45:00'
    DECLARE @end dateTime = '20201023 19:15:00'
    DECLARE @startDate Date = CAST(@start as date)
    DECLARE @endDate Date = CAST(@end as date)
    DECLARE @startTime Time = CAST(@start as time)
    DECLARE @endTime Time = CAST(@end as time)
    DECLARE @startOfDay DateTime = '08:00:00'
    DECLARE @endOfDay DateTime = '16:00:00';
    
    
    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 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
    ,
    dates AS
    (
    SELECT 
      DateAdd(day,n-1,@startDate) as [Day]
    FROM tally
    WHERE n<=DATEDIFF(day,@startDate,@endDate)+1
    )
    select 
      [Day],
      CASE WHEN [Day]=@startDate THEN @start ELSE CAST([DAY] AS DateTime)+@startOfDay END as [startOfWork],
      CASE WHEN [Day]=@endDate THEN @end ELSE CAST([DAY] AS DateTime)+@endOfDay END as [endOfWork]
    
    FROM dates

result:

| Day        | startOfWork             | endOfWork               |
|------------|-------------------------|-------------------------|
| 2020-10-21 | 2020-10-21 14:45:00.000 | 2020-10-21 16:00:00.000 |
| 2020-10-22 | 2020-10-22 08:00:00.000 | 2020-10-22 16:00:00.000 |
| 2020-10-23 | 2020-10-23 08:00:00.000 | 2020-10-23 19:15:00.000 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You can use a recursive CTE:

with dates as (
      select @STARTDATETIME as startdt,
             (case when datediff(day, @STARTDATETIME, @ENDDATETIME) = 0
                   then @ENDDATETIME
                   else dateadd(day, 1, convert(date, @STARTDATETIME))
              end) as enddt
      union all
      select enddte,
             (case when datediff(day, enddte, @ENDDATETIME) = 0
                   then @ENDDATETIME
                   else dateadd(day, 1, convert(date, enddte))
              end) as enddt,
             @ENDDATETIME as enddatetime
      from dates
      where enddt < @enddatetime
     )
select *
from date;

Upvotes: 3

Related Questions