How to select sales during banking hours with Microsoft SQL Server
How to get the sales for the month of May 2007, but only during the 'banking hours' of 10am and 3pm?
Data used
USE sql911;
go
SELECT emp_short, sales_date, sales_qty, sales_price FROM sales
WHERE sales_date between '2007-05-01' and '2007-05-31T23:59:59.999'
ORDER BY emp_short, sales_date;
go
go
SELECT emp_short, sales_date, sales_qty, sales_price FROM sales
WHERE sales_date between '2007-05-01' and '2007-05-31T23:59:59.999'
ORDER BY emp_short, sales_date;
go
- These are the sales from 1-May-2007 at midnight to 31-May-2007 at 11:59pm.
emp_short sales_date sales_qty sales_price ---------- ----------------------- --------------------------------------- --------------------------------------- 000010 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-08 05:00:00.000 1.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 2007-05-08 15:00:00.000 1.50 11.75 000030 2007-05-01 00:00:00.000 5.00 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000110 2007-05-09 14:00:00.000 1.25 10.00 000190 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 4.00 10.00 000190 2007-05-12 15:00:00.000 2.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 (19 row(s) affected)
Wrong way of running the select query with MSSQL
SELECT emp_short, sales_date, sales_qty, sales_price FROM sales
WHERE sales_date between '2007-05-01T9:30:00' and '2007-05-31T14:59:59.999'
ORDER BY emp_short, sales_date;
go
WHERE sales_date between '2007-05-01T9:30:00' and '2007-05-31T14:59:59.999'
ORDER BY emp_short, sales_date;
go
emp_short sales_date sales_qty sales_price ---------- ----------------------- --------------------------------------- --------------------------------------- 000010 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-08 05:00:00.000 1.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 2007-05-08 15:00:00.000 1.50 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000110 2007-05-09 14:00:00.000 1.25 10.00 000190 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 4.00 10.00 000190 2007-05-12 15:00:00.000 2.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 (18 row(s) affected)
- This is not right because of the times:
000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-08 05:00:00.000 1.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-08 05:00:00.000 1.00 10.00
We only want the data between 9:30am and 3:00pm
Right way of running the select query with MSSQL
Microsoft SQL Server has rich date functions. DATEPART let you 'slice and dice' the dates any way you want.
SELECT emp_short, sales_date, sales_qty, sales_price FROM sales
WHERE DATEPART(hh,sales_date) BETWEEN 9 AND 15 and
DATEPART(MONTH,sales_date) = 5 and
DATEPART(YEAR,sales_date) = 2007
ORDER BY emp_short, sales_date;
go
WHERE DATEPART(hh,sales_date) BETWEEN 9 AND 15 and
DATEPART(MONTH,sales_date) = 5 and
DATEPART(YEAR,sales_date) = 2007
ORDER BY emp_short, sales_date;
go
emp_short sales_date sales_qty sales_price ---------- ----------------------- --------------------------------------- --------------------------------------- 000010 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 2007-05-08 15:00:00.000 1.50 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000110 2007-05-09 14:00:00.000 1.25 10.00 000190 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 4.00 10.00 000190 2007-05-12 15:00:00.000 2.00 10.00 000310 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 (12 row(s) affected)
- You need to split the date from the time, since we want to select on both separately.
- You can use the same principle for selecting by week with DATEPART(week,sales_date) or by weekday DATEPART(weekday,sales_date) or by DATEPART(quarter,sales_date).