SQL - How to ignore seconds and round down minutes in DateTime data type

Question

At work we did a project that required a team to count students 8 times a day over 5 days at specific time periods. They are, as follows :-

09:00, 10:00, 11:00, 13:15, 14:15, 14:50, 15:50, 16:20.

Now, the data collected was put directly into a database via a web app. The problem is that database recorded each record using the standard YYYY-MM-DD HH:MM:SS.MIL, but if I were to order the records by date and then by student count it would cause the following problem; e.g.:-

if the students counted in a room was 5 at 09:00:12, but another room had a count of 0 at 09:02:20 and I did the following:

select student_count, audit_date from table_name order by audit_date, student_count;

The query will return:

5 09:00:12 0 09:02:20

but I want:

0 09:00:00 5 09:00:00

because we're looking for the number of students in each room for the period 09:00, but unfortunately to collect the data it required us to do so within that hour and obviously the database will pick up on that accuracy. Furthermore, this issue becomes more problematic when it gets to the periods 14:15 and 14:50, where we will need to be able to distinguish between the two periods.

Is there a way to ignore the seconds part of the DateTime, and the round the minutes down to the nearest ten minute?

I'm using SQL Server Management Studio 2012. If none of this made sense, I'm sorry!

CElliott · Accepted Answer · 2014-03-28 13:48:03Z

You may want some sort of Period table to store your segments. Then you can use that to join to your counts table.

CREATE TABLE [Periods] ( -- maybe [id] INT, [start_time] TIME, [end_time] TIME ); INSERT INTO [Periods] VALUES ('09:00','10:00'), ('10:00','11:00'), ('11:00','13:15'), ('13:15','14:15'), ('14:15','14:50'), ('14:50','15:50'), ('15:50','16:20'), ('16:20','17:00') SELECT student_count, [start_time] FROM table_name A INNER JOIN [Periods] B ON CAST(A.[audit_date] AS TIME) >= B.[start_time] AND CAST(A.[audit_date] AS TIME) < B.[end_time]

Thanks for this, I had to make some changes because the actual database is SQL Server 2005 and therefore the time data type doesn't exist; ended up using DATEPART for the [start_time] column

jpw · Accepted Answer · 2014-03-28 13:46:08Z

You can use the DATEADDand DATEPARTfunctions to accomplish this together with a CASEexpression. If you want more precise cutoffs between the .14and .50periods you can easily adjust the case statement and also if you want to minutes to be .10or.15

-- some test data declare @t table (the_time time) insert @t values ('09:00:12') insert @t values ('14:16:12') insert @t values ('09:02:12') insert @t values ('14:22:12') insert @t values ('15:49:12') insert @t values ('15:50:08') select the_time, case when datepart(minute,the_time) < 15 then dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time),the_time)) when datepart(minute,the_time) >= 15 and datepart(minute,the_time) < 50 then dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time)+10,the_time)) else dateadd(second, -datepart(second,the_time),dateadd(minute, -datepart(minute,the_time)+50,the_time)) end as WithoutSeconds from @t

Results:

the_time WithoutSeconds ---------------- ---------------- 09:00:12.0000000 09:00:00.0000000 14:16:12.0000000 14:10:00.0000000 09:02:12.0000000 09:00:00.0000000 14:22:12.0000000 14:10:00.0000000 15:49:12.0000000 15:10:00.0000000 15:50:08.0000000 15:50:00.0000000

Marc · Accepted Answer · 2014-03-28 13:46:22Z

Try this:

SELECT CAST( DATEADD(SECOND, - (CONVERT(INT, RIGHT(CONVERT(CHAR(2), DATEPART(MINUTE, GETDATE())),1))*60) - (DATEPART(SECOND,GETDATE())), GETDATE()) AS SMALLDATETIME);

EricZ · Accepted Answer · 2014-03-28 14:13:47Z

You can try ORDER BY this formula

DATEADD(minute, floor((DATEDIFF(minute, '20000101', audit_date) + 5)/10)*10, '20000101')

e.g.

WITH tbl AS( SELECT * FROM ( VALUES (5,'2014-03-28 09:00:09.793'),(0,'2014-03-28 09:02:20.123')) a (student_count, audit_date) ) SELECT *,DATEADD(minute, floor((DATEDIFF(minute, '20000101', audit_date) + 5)/10)*10, '20000101') as ORDER_DT FROM tbl ORDER BY ORDER_DT,student_count

SQL Fiddle

Collectives™ on Stack Overflow

SQL - How to ignore seconds and round down minutes in DateTime data type

4 Answers 4

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Related