How to only select 11pm to 7am in DateTime (many months of data) DDM&DDL included :)

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 24, 2017 at 4:01 pm

  #329273

  Looking to run a report for a few years but only between 11pm and 7am.

  CREATE TABLE mytable(
 i_ticket_id  INTEGER NOT NULL PRIMARY KEY
,c_grand_total MONEY(6,2) NOT NULL
,c_payment_total MONEY(6,2) NOT NULL
,dt_close_time DateTime(23) NOT NULL
);
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');


  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time > '2017-10-23 21:57:40.323'

  I have this effort, but I get a syntax error (for obvious reasons)
  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time between '2015-10-23 04:00:00.000' and '2015-10-23 04:00:00.000' AND (DATEPART(hh, dt_close_time) between '23:00:00.000' and '07:00:00.000'

  Hope my data is better formatted 🙂

  Chris

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 4:03 pm

  #1965017

  chef423 - Tuesday, October 24, 2017 4:01 PM

  CREATE TABLE mytable(
 i_ticket_id  INTEGER NOT NULL PRIMARY KEY
,c_grand_total NUMERIC(6,2) NOT NULL
,c_payment_total NUMERIC(6,2) NOT NULL
,dt_close_time VARCHAR(23) NOT NULL
);
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');


  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time > '2017-10-23 21:57:40.323'

  I have this effort, but I get a syntax error (for obvious reasons)
  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time between '2015-10-23 04:00:00.000' and '2015-10-23 04:00:00.000' AND (DATEPART(hh, dt_close_time) between '23:00:00.000' and '07:00:00.000'

  Hope my data is better formatted 🙂

  Chris

  Based on the data posted what do you expect to be returned?

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 24, 2017 at 4:04 pm

  #1965018

  Lynn Pettis - Tuesday, October 24, 2017 4:03 PM

  chef423 - Tuesday, October 24, 2017 4:01 PM

  CREATE TABLE mytable(
 i_ticket_id  INTEGER NOT NULL PRIMARY KEY
,c_grand_total NUMERIC(6,2) NOT NULL
,c_payment_total NUMERIC(6,2) NOT NULL
,dt_close_time VARCHAR(23) NOT NULL
);
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');


  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time > '2017-10-23 21:57:40.323'

  I have this effort, but I get a syntax error (for obvious reasons)
  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time between '2015-10-23 04:00:00.000' and '2015-10-23 04:00:00.000' AND (DATEPART(hh, dt_close_time) between '23:00:00.000' and '07:00:00.000'

  Hope my data is better formatted 🙂

  Chris

  Based on the data posted what do you expect to be returned?

  Only sales from 11pm to 7am, Lynn.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 24, 2017 at 4:08 pm

  #1965019

  Current output:
  i_ticket_id,c_grand_total,c_payment_total,dt_close_time
  215670,0.00,0.00,2017-10-23 22:03:24.553
  215644,133.28,133.28,2017-10-23 22:19:57.710
  215671,42.61,42.61,2017-10-23 22:27:04.323
  215673,45.42,45.42,2017-10-23 22:27:38.307
  215672,0.00,0.00,2017-10-23 22:31:52.507
  215666,68.83,68.83,2017-10-23 23:00:19.120
  215610,412.96,412.96,2017-10-23 23:01:17.790

  I really want to get good at posting in the correct format. Meaning, what you guys require to assist. Plus, it will make me better at writing the code.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 4:12 pm

  #1965021

  chef423 - Tuesday, October 24, 2017 4:01 PM

  Looking to run a report for a few years but only between 11pm and 7am.

  CREATE TABLE mytable(
 i_ticket_id  INTEGER NOT NULL PRIMARY KEY
,c_grand_total NUMERIC(6,2) NOT NULL
,c_payment_total NUMERIC(6,2) NOT NULL
,dt_close_time VARCHAR(23) NOT NULL
);
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');


  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time > '2017-10-23 21:57:40.323'

  I have this effort, but I get a syntax error (for obvious reasons)
  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time between '2015-10-23 04:00:00.000' and '2015-10-23 04:00:00.000' AND (DATEPART(hh, dt_close_time) between '23:00:00.000' and '07:00:00.000'

  Hope my data is better formatted 🙂

  Chris

  Also, store your date/time values using a date/time data type, not a varchar string.  Use the right data type for data being stored.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 24, 2017 at 4:22 pm

  #1965022

  Lynn Pettis - Tuesday, October 24, 2017 4:12 PM

  chef423 - Tuesday, October 24, 2017 4:01 PM

  Looking to run a report for a few years but only between 11pm and 7am.

  CREATE TABLE mytable(
 i_ticket_id  INTEGER NOT NULL PRIMARY KEY
,c_grand_total NUMERIC(6,2) NOT NULL
,c_payment_total NUMERIC(6,2) NOT NULL
,dt_close_time VARCHAR(23) NOT NULL
);
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');


  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time > '2017-10-23 21:57:40.323'

  I have this effort, but I get a syntax error (for obvious reasons)
  select i_ticket_id, c_grand_total, c_payment_total, dt_close_time from Ticket
where b_closed = 1 and i_void_ticket_id is null and dt_close_time between '2015-10-23 04:00:00.000' and '2015-10-23 04:00:00.000' AND (DATEPART(hh, dt_close_time) between '23:00:00.000' and '07:00:00.000'

  Hope my data is better formatted 🙂

  Chris

  Also, store your date/time values using a date/time data type, not a varchar string.  Use the right data type for data being stored.

  Ok, ill re-do it. I thought it set the correct output using the CSV to SQL Converter.

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  October 24, 2017 at 4:56 pm

  #1965026

  Couple things jump out as odd... First:
  dt_close_time VARCHAR(23)

  Why not use a DATETIME datatype?
  Second, there seem to be only two records that meet the criteria (or did I do something stupid?).

  Took the liberty of redefinining some of your datatypes in your table:
  CREATE TABLE mytable(
i_ticket_id INTEGER NOT NULL PRIMARY KEY
,c_grand_total SMALLMONEY NOT NULL
,c_payment_total SMALLMONEY NOT NULL
,dt_close_time DATETIME NOT NULL
);
GO

  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215644,133.28,133.28,'2017-10-23 22:19:57.710');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215671,42.61,42.61,'2017-10-23 22:27:04.323');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215673,45.42,45.42,'2017-10-23 22:27:38.307');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215672,0.00,0.00,'2017-10-23 22:31:52.507');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215666,68.83,68.83,'2017-10-23 23:00:19.120');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215610,412.96,412.96,'2017-10-23 23:01:17.790');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215654,26.22,26.22,'2017-10-23 23:02:30.340');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215636,180.26,180.26,'2017-10-23 23:25:19.383');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215663,30.59,30.59,'2017-10-23 23:25:47.847');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215677,49.16,49.16,'2017-10-23 23:26:26.113');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215675,42.61,42.61,'2017-10-23 23:27:44.350');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215667,10.92,10.92,'2017-10-23 23:28:24.357');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215674,5.46,5.46,'2017-10-23 23:28:49.140');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215621,57.90,57.90,'2017-10-23 23:29:22.160');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215637,159.50,159.50,'2017-10-23 23:35:11.973');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215676,34.96,34.96,'2017-10-23 23:55:46.433');
  INSERT INTO mytable(i_ticket_id,c_grand_total,c_payment_total,dt_close_time) VALUES (215678,90.00,90.00,'2017-10-24 14:33:51.800');

  If x.hr<=7 returns no records... 

  SELECT *
FROM (
SELECT i_ticket_id
    , dt_close_time
    , DATEPART(HOUR,dt_close_time) AS hr
FROM mytable
) x
WHERE x.hr<=7
OR x.hr>=23;

  When you create a sample dataset, make sure you have data for all the conditions you're testing for. =)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 5:16 pm

  #1965030

  pietlinden - Tuesday, October 24, 2017 4:56 PM

  Couple things jump out as odd... First:
  dt_close_time VARCHAR(23)

  Why not use a DATETIME datatype?
  Second, there seem to be only two records that meet the criteria (or did I do something stupid?).

  I get twelve rows of data from the sample data.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 5:18 pm

  #1965031

  Here is my code:
  
SELECT
[m].*
FROM
mytable AS [m]
WHERE
[m].[dt_close_time] >= DATEADD(HOUR,23,DATEADD(DAY,DATEDIFF(DAY,0,[m].[dt_close_time]),0)) AND
[m].[dt_close_time] < DATEADD(HOUR,31,DATEADD(DAY,DATEDIFF(DAY,0,[m].[dt_close_time]),0))
ORDER BY [m].[dt_close_time];


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 5:21 pm

  #1965034

  There may be a better way that is more performant and scalable, but I don't have the time at the moment as I am also working.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  October 24, 2017 at 7:13 pm

  #1965039

  If the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 7:29 pm

  #1965040

  TheSQLGuru - Tuesday, October 24, 2017 7:13 PM

  If the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.

  Remember we are going across time boundaries.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  October 24, 2017 at 8:00 pm

  #1965042

  If query performance is an issue, consider adding just the time portion as a calculated column to the base table. That way you'll be able to include it in indexes.
  CREATE TABLE mytable (
    i_ticket_id INTEGER NOT NULL PRIMARY KEY,
    c_grand_total SMALLMONEY NOT NULL,
    c_payment_total SMALLMONEY NOT NULL,
    dt_close_time DATETIME NOT NULL,
    t_close_time AS CAST(dt_close_time AS TIME(0))
);
GO
CREATE UNIQUE NONCLUSTERED INDEX uix_mytable_cltime_ticketid
ON dbo.mytable (
    t_close_time,
    dt_close_time,
    i_ticket_id
    )
INCLUDE (
    c_grand_total,
    c_payment_total
    )
WITH (DROP_EXISTING = ON);

  SELECT
    *
FROM
    dbo.mytable m
WHERE
    m.t_close_time >= '07:00'
    AND m.t_close_time <= '23:00';

  OBJECT: ([tempdb].[dbo].[mytable].[uix_mytable_cltime_ticketid] AS [m]), SEEK: ([m].[t_close_time] > [Expr1009] AND [m].[t_close_time] < [Expr1010]) ORDERED FORWARD

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2017 at 8:39 pm

  #1965046

  Jason A. Long - Tuesday, October 24, 2017 8:00 PM

  If query performance is an issue, consider adding just the time portion as a calculated column to the base table. That way you'll be able to include it in indexes.
  CREATE TABLE mytable (
    i_ticket_id INTEGER NOT NULL PRIMARY KEY,
    c_grand_total SMALLMONEY NOT NULL,
    c_payment_total SMALLMONEY NOT NULL,
    dt_close_time DATETIME NOT NULL,
    t_close_time AS CAST(dt_close_time AS TIME(0))
);
GO
CREATE UNIQUE NONCLUSTERED INDEX uix_mytable_cltime_ticketid
ON dbo.mytable (
    t_close_time,
    dt_close_time,
    i_ticket_id
    )
INCLUDE (
    c_grand_total,
    c_payment_total
    )
WITH (DROP_EXISTING = ON);

  SELECT
    *
FROM
    dbo.mytable m
WHERE
    m.t_close_time >= '07:00'
    AND m.t_close_time <= '23:00';

  OBJECT: ([tempdb].[dbo].[mytable].[uix_mytable_cltime_ticketid] AS [m]), SEEK: ([m].[t_close_time] > [Expr1009] AND [m].[t_close_time] < [Expr1010]) ORDERED FORWARD

  Where clause looks wrong.  OP wants all data from 23:00 to 7:00 the following morning.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  October 24, 2017 at 9:45 pm

  #1965049

  Lynn Pettis - Tuesday, October 24, 2017 7:29 PM

  TheSQLGuru - Tuesday, October 24, 2017 7:13 PM

  If the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.

  Remember we are going across time boundaries.

  I thought it was a single-value predicate of >23 OR < 7? I note that I have been mostly brain dead all evening. 🙂

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service