Time between 12.00 AM to 03.30 AM

Category: sql server search

Question

Rami Reddy G on Fri, 19 Dec 2014 13:08:20


IN my table I have column name like 'DateTime', I want to SELECT the time from 00:00:00.000 to 03:30:00.000 with out considering the "Year-Month-Date" Considering only Time????

For your Reference column data as shown below:-

2014-12-18 21:51:11.000
2014-12-18 10:21:58.000
2014-12-18 10:12:54.000
2014-12-18 10:11:53.000
2014-12-18 08:24:36.000
2014-12-18 07:45:26.000
2014-12-18 07:44:56.000
2014-12-18 07:44:26.000
2014-12-18 07:43:56.000
2014-12-18 07:12:02.000
2014-12-17 21:51:04.000
2014-12-17 14:13:01.000
2014-12-17 12:51:53.000
2014-12-17 11:03:01.000
2014-12-17 10:33:02.000
2014-12-17 10:32:42.000
2014-12-17 01:06:19.000
2014-12-17 00:56:23.000
2014-12-17 00:55:19.000
2014-12-17 00:47:52.000
2014-12-16 13:23:21.000
2014-12-16 13:21:07.000
2014-12-16 04:47:14.000
2014-12-16 03:33:17.000
2014-12-16 03:31:07.000
2014-12-16 00:01:36.000
2014-12-15 21:50:53.000
2014-12-15 16:22:15.000
2014-12-15 16:22:08.000
2014-12-15 11:12:21.000
2014-12-15 09:13:06.000
2014-12-15 07:02:57.000
2014-12-15 00:01:37.000

Replies

Eric__Zhang on Sat, 20 Dec 2014 05:46:14


Hi Rami,

The query below can achieve your requirement.

SELECT * FROM yourTable WHERE CONVERT(varchar(12),[datetime], 114) < '03:30:00.000'

see more details about datetime CONVERT by click here.

If you have any question, feel free to let me know.

Best regards,
Eric Zhang

Jackson_1990 on Sat, 20 Dec 2014 06:30:06


IN my table I have column name like 'DateTime', I want to SELECT the time from 00:00:00.000 to 03:30:00.000 with out considering the "Year-Month-Date" Considering only Time????

For your Reference column data as shown below:-

2014-12-18 21:51:11.000
2014-12-18 10:21:58.000
2014-12-18 10:12:54.000
2014-12-18 10:11:53.000
2014-12-18 08:24:36.000
2014-12-18 07:45:26.000
2014-12-18 07:44:56.000
2014-12-18 07:44:26.000
2014-12-18 07:43:56.000
2014-12-18 07:12:02.000
2014-12-17 21:51:04.000
2014-12-17 14:13:01.000
2014-12-17 12:51:53.000
2014-12-17 11:03:01.000
2014-12-17 10:33:02.000
2014-12-17 10:32:42.000
2014-12-17 01:06:19.000
2014-12-17 00:56:23.000
2014-12-17 00:55:19.000
2014-12-17 00:47:52.000
2014-12-16 13:23:21.000
2014-12-16 13:21:07.000
2014-12-16 04:47:14.000
2014-12-16 03:33:17.000
2014-12-16 03:31:07.000
2014-12-16 00:01:36.000
2014-12-15 21:50:53.000
2014-12-15 16:22:15.000
2014-12-15 16:22:08.000
2014-12-15 11:12:21.000
2014-12-15 09:13:06.000
2014-12-15 07:02:57.000
2014-12-15 00:01:37.000

Try
substring(CONVERT(varchar,date_column,114),1,5) between '00:00' and '03:30'