Solved

# Calculating Business Hours

Posted on 2016-11-19
Medium Priority
123 Views
Last Modified: 2016-12-03
Hi

Using SQL 2008

I am trying to calculate the time in hours between two datetime fields.

However the query must take into account business hours

These are Mon - Fri 07:00 to 00:00 (midnight)
Saturday and Sunday and Public Holidays 08:00 to 18:00

Example

Open Date                                  Close Date
2016-11-17 23:00:00.000            2016-11-18 08:00:00.000

The result would be 2

Any help would be appreciated
0
Question by:halifaxman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 8
• 7
• 2
• +2
19 Comments

LVL 32

Expert Comment

ID: 41894125
You can use

DATEDIFF ( hh , StartDate , EndDate  )

If you could provide some data and the expected output, we shall do it for you.
0

LVL 32

Expert Comment

ID: 41894182
Complete.. Please try..

Table Creation

``````CREATE TABLE Test
(
Id INT
,StartDate DATETIME
,EndDate DATETIME
)
GO

INSERT INTO Test VALUES
( 1 ,  '2016-11-17 23:00:00.000'     ,       '2016-11-18 08:00:00.000' ),
( 2 ,  '2016-11-19 08:00:00.000'     ,       '2016-11-19 12:00:00.000' )
GO
``````

Query

``````DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
,CTE0 AS
(
SELECT *, DATEADD(d,Number,@StartDate) st FROM Series
)
,CTE1 AS
(
SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate
FROM Test
)
,CTE2 AS
(
SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt , DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
SELECT * ,
CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN StartDate > stt THEN StartDate ELSE stt END
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')

END Starts
,
CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN EndDate < ett THEN EndDate ELSE ett END
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
END Ends
FROM CTE2
)
SELECT Id,SUM(hrx) Hours FROM
(
SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx FROM CTE3
)k GROUP BY Id
``````

Output

``````Id          Hours
----------- ---------------------------------------
1           2.000000
2           4.000000

(2 row(s) affected)
``````

Hope it helps !!
0

Author Comment

ID: 41894685
Thanks will give it a go today
0

Author Comment

ID: 41894818
Hi

Ran this on my data - it part works

The query does not take into account Saturdays and Sundays - 08:00 to 18:00 - they are present calculating at weekday opening hours i.e.
17 hours

Also  Bank Holidays such as Christmas Day, Good Friday, Easter Monday need to be taken into account they will be same hrs as Saturday and Sunday 08:00 to 18:00 hrs

The query runs slow at the moment - 15 minutes at present

Thanks for your help on this
0

LVL 32

Expert Comment

ID: 41895366
Saturday , Sunday & Bank Holidays such as Christmas Day, Good Friday, Easter Monday need to be taken

Handled both. For holidays I have created a new Holiday table and inserted 3 rows. you can insert all your data and run the query and check the output.

``````CREATE TABLE Holiday
(
Dt DATE
,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
,CTE0 AS
(
SELECT *, DATEADD(d,Number,@StartDate) st FROM Series
)
,CTE1 AS
(
SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate
FROM Test
)
,CTE2 AS
(
SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt , DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
SELECT * ,
CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN StartDate > stt THEN StartDate ELSE stt END
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')

END Starts
,
CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN EndDate < ett THEN EndDate ELSE ett END
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
END Ends
FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM
(
SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx  , DATEPART(dw,Starts) a,DATEPART(dw,Ends) b
FROM CTE3
WHERE
DATEPART(dw,Starts) NOT IN (7,8)
AND DATEPART(dw,Ends) NOT IN (7,8)
AND Starts NOT IN (SELECT Dt FROM Holiday)
AND Ends NOT IN (SELECT Dt FROM Holiday)
)k GROUP BY Id
``````

The query runs slow at the moment - 15 minutes at present

Once the code is finalized then we shall check for query performance.
0

Author Comment

ID: 41895485
Brilliant thanks for your help with this - will try this out today
0

Author Comment

ID: 41895532
Hi

This is not working properly

Couple of examples

ID                     Logged                                         Completed                                  Hours
I1605-4263      2016-05-10 19:25:00.000            2016-05-17 13:00:00.000            4.600000
I1606-5092      2016-06-10 16:43:00.000            2016-06-13 10:00:00.000            7.300000

I1605-4263 should be
10th May - 4.6 hrs
11th May - 17 hrs
12th May - 17 hrs
13th May - 17 hrs
14th May - 10 hrs (Saturday)
15th May - 10 hrs (Sunday)
16th May - 17 hrs
17th May - 6 hrs

Total = 98.6 hrs

I1606-5092 should be
10th June - 7.3 hrs
11th June - 10 hrs (Saturday)
12th June - 10 hrs (Sunday)
13th June - 3hrs

Total 30.3 hrs

Thanks
0

LVL 32

Expert Comment

ID: 41895538
Do you want to include Sat/Sunday?
0

Author Comment

ID: 41895554
Hi

Yes

Saturday and Sunday and Public Holidays the hours are different from weekdays - Sat/Sun/Public Holidays are 08:00 to 18:00 (10 hrs)

Thanks
0

LVL 32

Expert Comment

ID: 41895562
Ok. try..

``````CREATE TABLE Holiday
(
Dt DATE
,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
,CTE0 AS
(
SELECT *, DATEADD(d,Number,@StartDate) st FROM Series
)
,CTE1 AS
(
SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate
FROM Test
)
,CTE2 AS
(
SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt
, DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
SELECT * ,DATEPART(dw,StartDate) d1,
CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN StartDate > stt THEN StartDate ELSE stt END
ELSE
CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
END

END Starts
,
CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN EndDate < ett THEN EndDate ELSE ett END
ELSE
CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '18:00:00')
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
END
END Ends
FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM
(
SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx
FROM CTE3
)k GROUP BY Id
``````
0

Author Comment

ID: 41895749
Hi

This is still counting Saturday and Sunday as 17 instead of 10 hrs

Example

ID              Logged                                               Completed                              Hours
I1601-7725      2016-01-27 14:53:00.000      2016-02-01 22:02:00.000      92.100000

This should be 77.1 hrs

Thanks
0

LVL 32

Expert Comment

ID: 41895771
Pls try..

``````CREATE TABLE Holiday
(
Dt DATE
,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
,CTE0 AS
(
SELECT *, DATEADD(d,Number,@StartDate) st FROM Series
)
,CTE1 AS
(
SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate
FROM Test
)
,CTE2 AS
(
SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt
, DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
SELECT * ,DATEPART(dw,StartDate) d1,
CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN StartDate > stt THEN

CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, -1, CAST(StartDate AS DATE)), '08:00:00')
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(StartDate AS DATE)), '07:00:00')
END

ELSE

CASE WHEN DATEPART(dw,stt) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
END

END
ELSE
CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
ELSE
DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
END

END Starts
,
CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN

CASE WHEN EndDate < ett THEN

CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, 0, CAST(EndDate AS DATE)), '18:00:00')
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(EndDate AS DATE)), '23:59:59')
END

ELSE

CASE WHEN DATEPART(dw,ett) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, 0, CAST(ett AS DATE)), '18:00:00')
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(ett AS DATE)), '23:59:59')
END

END
ELSE
CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '18:00:00')
ELSE
DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
END
END Ends
FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM
(
SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx
FROM CTE3
)k GROUP BY Id
``````
0

Author Comment

ID: 41895829
Hi

It seems to be treating every day as Saturday and Sunday
also if the Logged and Completed days are on the same day its giving it a value of -7

Example

ID                     Logged                                          Completed                                    Hours
I1609-1396      2016-09-05 07:56:00.000            2016-09-05 08:25:00.000            -7.000000
I1609-2078      2016-09-05 14:52:00.000            2016-09-06 15:17:00.000            10.000000

1396 should be 0.5 hrs
2078 should be 17 hrs

Thanks
0

LVL 32

Expert Comment

ID: 41896061
Could you please post list of rows and the expected data?
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 41896734
I think the query below will do it.  I used an inline tally table to cover multiple days.  I only used a max of 10 days but it's easy enough to extend the tally table to 100 or even 1000 rows.  I used CROSS APPLYs to do the preliminary calcs to make the main SELECT code "less cluttered".

--"Holidays" structure, but no data; naturally use your real holiday table or load dates to the one below.
--!!!!!Make sure you don't run the next line if your real holidays table is named "dbo.Holidays"!!!!
IF OBJECT_ID('dbo.Holidays') IS NOT NULL DROP TABLE dbo.Holidays;
CREATE TABLE dbo.Holidays ( date date PRIMARY KEY );

--"Test" structure and data.
IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
CREATE TABLE dbo.Test ( Id varchar(20), Logged datetime, Completed datetime );
INSERT INTO dbo.Test VALUES
( 'I1605-4263', '2016-05-10 19:25:00.000', '2016-05-17 13:00:00.000' ),
( 'I1606-5092', '2016-06-10 16:43:00.000', '2016-06-13 10:00:00.000' );

--actual query to calc hours
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS numbers(number)
)
SELECT
ID,
/* showing each day just for reference: you could instead sum up hours for all dates */
work_date,
CASE WHEN work_start_time > last_work_hour THEN 0.0
ELSE ROUND(DATEDIFF(MINUTE,
CASE WHEN work_start_time < first_work_hour THEN first_work_hour ELSE work_start_time END,
CASE WHEN work_end_time > last_work_hour THEN last_work_hour ELSE work_end_time END) / 60.0, 1)
END AS hours_worked,
/* just for reference */
logged, completed
FROM dbo.Test t
INNER JOIN cteTally10 days ON days.number BETWEEN 0 AND DATEDIFF(DAY, t.Logged, t.Completed)
CROSS APPLY (
SELECT CAST(DATEADD(DAY, days.number, t.Logged) AS date) AS work_date
) AS assign_alias_names_1
CROSS APPLY (
SELECT CAST(CASE WHEN days.number = 0 /*first day*/ THEN t.Logged - work_date
ELSE '00:00' END AS smalldatetime) AS work_start_time,
CAST(CASE WHEN days.number = DATEDIFF(DAY, t.Logged, t.Completed) /*last day*/ THEN t.Completed - work_date
ELSE '19000102' END AS smalldatetime) AS work_end_time
) AS assign_alias_names_2
CROSS APPLY (
SELECT CAST(CASE
WHEN DATEDIFF(DAY, 0, work_date) % 7 IN (5, 6) /*Saturday,Sunday*/
THEN 1
WHEN EXISTS(SELECT 1 FROM Holidays h WHERE h.date = work_date)
THEN 1
ELSE 0 END AS bit) AS is_weekend_or_holiday
) AS assign_alias_names_3
CROSS APPLY (
SELECT CAST(CASE WHEN is_weekend_or_holiday = 0 THEN '07:00' ELSE '08:00' END AS smalldatetime) AS first_work_hour,
CAST(CASE WHEN is_weekend_or_holiday = 0 THEN '19000102' ELSE '18:00' END AS smalldatetime) AS last_work_hour
) AS assign_alias_names_4
ORDER BY ID, work_date
0

LVL 15

Expert Comment

ID: 41896811
My effort, calculating hours generating a cte that has all the possible work days and hours in it.  I think the queries at the end could probably  take some optimisation and I made an issue with having the end times as 23:59 which is why I am converting between minutes and hours and adding 1s <sigh>

Table build and population:
``````CREATE DATABASE Test18

USE Test18;

CREATE TABLE workHours(
ID INT IDENTITY(1,1)
, [Day] INT
, startHour TIME
, endHour TIME)

CREATE TABLE holiday(
ID INT IDENTITY(1,1)
, [Date] DATE
, startHour TIME
, endHour TIME)

CREATE TABLE [example](
exampleID INT IDENTITY(1,1)
, startHour datetime
, endHour datetime)
GO

INSERT INTO workHours VALUES (1, '08:00', '18:00')
INSERT INTO workHours VALUES (2, '07:00', '23:59')
INSERT INTO workHours VALUES (3, '07:00', '23:59')
INSERT INTO workHours VALUES (4, '07:00', '23:59')
INSERT INTO workHours VALUES (5, '07:00', '23:59')
INSERT INTO workHours VALUES (6, '07:00', '23:59')
INSERT INTO workHours VALUES (7, '08:00', '18:00')

INSERT INTO holiday VALUES('2016-12-25', '08:00', '18:00')
INSERT INTO holiday VALUES('2016-12-26', '08:00', '18:00')
INSERT INTO holiday VALUES('2017-01-01', '08:00', '18:00')

INSERT INTO example VALUES('2016-11-17 23:00:00.000','2016-11-18 08:00:00.000')
INSERT INTO example VALUES('2016-11-18 08:00:00.000','2016-11-18 11:00:00.000')
INSERT INTO example VALUES('2016-11-19 23:00:00.000','2016-11-23 14:00:00.000')
INSERT INTO example VALUES('2016-11-27 15:00:00.000','2016-12-14 19:00:00.000')
INSERT INTO example VALUES('2016-12-13 08:00:00.000','2016-12-18 12:00:00.000')
INSERT INTO example VALUES('2016-12-22 11:00:00.000','2016-12-26 22:00:00.000')
INSERT INTO example VALUES('2016-12-29 18:00:00.000','2017-01-03 17:00:00.000')
``````
Query to run:
``````USE TEST18;

DECLARE @currentDate DATE
SELECT @currentDate=MIN(startHour) FROM example

DECLARE @cteWorkingTimes TABLE (
[Date] DATE
, startHour TIME
, endHour TIME
, WorkingHours INT)
--Build list of all possible dates and working hours based on the example table
WHILE (SELECT MAX(endHour) FROM example) > @currentDate
BEGIN
INSERT INTO @cteWorkingTimes ([Date], startHour, endHour, WorkingHours)
SELECT
@CurrentDate
, startHour
, endHour
, CASE DATEPART(N,endHour)
WHEN 59 THEN DATEDIFF(N,startHour,endHour)+1
ELSE DATEDIFF(N,startHour,endHour)
END WorkingHours
FROM
[dbo].[workHours]
WHERE
DATEPART(weekday,@currentDate) = [Day]

SELECT @currentDate = DATEADD(D,1,@currentDate)
END
--Update list of all posisble dates with holiday working
UPDATE c
SET
startHour=h.startHour
, endHour=h.endHour
, WorkingHours=DATEDIFF(N,h.startHour,h.endHour)
FROM
@cteWorkingTimes c
JOIN holiday h ON c.[Date]=h.[date]

SELECT
e.exampleID
, e.startHour
, e.endHour
, SUM(
CASE
WHEN CONVERT(DATE,e.startHour)=wt.[date] AND DATEPART(N,wt.endHour)=59 THEN DATEDIFF(N,CONVERT(TIME,e.startHour),wt.endHour)+1
WHEN CONVERT(DATE,e.startHour)=wt.[date] THEN DATEDIFF(N,CONVERT(TIME,e.startHour),wt.endHour)
WHEN CONVERT(DATE,e.endHour)=wt.[date] THEN DATEDIFF(N,wt.startHour,CONVERT(TIME,e.endHour))
ELSE wt.workingHours
END/60) [workingHours]
FROM
@cteWorkingTimes wt
JOIN example e ON wt.[date]>=CONVERT(DATE,e.startHour) AND wt.[date]<=CONVERT(DATE,e.endHour)
GROUP BY
e.exampleID
, e.startHour
, e.endHour
``````
0

Author Comment

ID: 41897143
Scott

Just had a look at the query posted and it works nicely - thanks

With regards to

I only used a max of 10 days but it's easy enough to extend the tally table to 100 or even 1000 rows.

I probably need to extend it to 500 rows - do I need to just keep adding (10),(11),(12) etc

Thanks
0

LVL 70

Expert Comment

ID: 41897585
do I need to just keep adding (10),(11),(12) etc

Nah.  Sorry, earlier I was busy and I took a short-cut on the earlier tally table code.  Numbering all rows ourselves is too inefficient for large numbers of rows.

Instead, let's just add another "standard" CROSS JOIN (10 rows * 10 rows * 10 rows = 1000 rows) and ROW_NUMBER() (for efficiency), which will give you up to 999 days.  Btw, this method can be efficiently extended to 1M rows if needed.

;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
CROSS JOIN cteTally10 c3
)
....
0

LVL 111

Expert Comment

ID: 41911426
On the outside chance that you could be using PHP, you might be interested in Practical Application #9 in this article.
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even wâ€¦
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month12 days, 10 hours left to enroll

#### 650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.