Comparing times - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing times

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2011-08-12 : 09:57:52

Hi,

I have a table full of bookings and each booking weither has a notification time or a notification period (which is the amount of time before the booking that the user should be notified).

I am trying to get all bookings that haven't had a notification sent, but the notification time must be within a 10 minute period. Here is what I have:

--other code above, obviously

WHERE b.NotificationSent = 0
AND b.AppointmentDateTime between @StartDate AND @EndDate

AND ((sl.NotificationPeriod = 0
AND (CONVERT(float,sl.NotificationTime) between
CONVERT(float,REPLACE(CONVERT(VARCHAR(5),DATEADD(mi, -5,GETDATE()),108), ':', '.'))
AND CONVERT(float,REPLACE(CONVERT(VARCHAR(5),DATEADD(mi, 5,GETDATE()),108), ':', '.'))))
OR (sl.NotificationTime is null AND
REPLACE(CONVERT(VARCHAR(5), dateadd(mi, sl.NotificationPeriod,getdate()),108),
':', '.') < sl.AppointmentTime))

So, the appointment needs to be today (StartDate and EndDate are always today, from midnight to midnight - one minute), and if the time now is 15.23 it should check for any bookings where the notification time is between 15.18 and 15.28. However, the above brings back ALL bookings for the day, even if the notification time is 16.00 or 14.00.

Can anyone help me do the comparison and filter these results?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-12 : 10:38:51

It may be simpler to use the DATEDIFF function (which would take into account the day and time - something like this)


WHERE 
	b.NotificationSent = 0 
	AND b.AppointmentDateTime BETWEEN @StartDate AND @EndDate
	AND sl.NotificationPeriod = 0
	AND DATEDIFF(minute, sl.NotificationTime, GETDATE()) BETWEEN -5 AND 5;

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-12 : 13:59:59

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> I have a table full of bookings and each booking whether has a notification time or a notification period (which is the amount of time before the booking that the user should be notified).

I am trying to get all bookings that haven't had a notification sent, but the notification time must be within a 10 minute period. Here is what I have:

>> --other code above, obviouS1y <<
NO, it is not! It looks like you are using flags in SQL, making temporal data into strings and a bunch of other bad design choices. Did you know we have DATE and TIME data types now? Probably not, if you are still using getdate() from the old Sybase/UNIX days of T-SQL. Also, why are you using FLOAT? Why do you think that single letters make readable aliases?

Since you feel you can be vague, I will return the favor:) Build a table of time slots and locate the time part of your appointments in that table. SQL is a data language and you want to do string and temporal computational programming! Why?

CREATE TABLE Timeslots
(timeslot_nbr SMALLINT NOT NULL PRIMARY KEY,
slot_start_time TIME(2) NOT NULL,
slot_end_time TIME(2) NOT NULL,
CHECK (slot_start_time < slot_end_time)
);

Space out your timeslots in whatever length you like (the magical 50-minute hour doctors use, for example).

>> So, the appointment needs to be today. @report_start_date and @report_end_date are always today, from midnight to midnight - one minute), <<

That is a DATE data type. But why limit things to just today? But okay:

>> .. and if the time now is 15:23 it should check for any bookings where the notification time is between 15:18 and 15:28. However, the above brings back ALL bookings for the day, even if the notification time is 16:00 or 14:00. <<

CREATE PROCEDURE Daily_Appointments()
AS
SELECT R.timeslot_nbr, A.appointment_id, ..
FROM Timeslots AS T, Appointments AS A, ..
WHERE CAST (A.appointment_datetime AS TIME(0))
BETWEEN T.slot_start_time AND T.slot_end_time -- current appointments
AND CAST (CURRENT_TIMESTAMP AS TIME(0)
BETWEEN T.slot_start_time AND T.slot_end_time -– current timeslot
AND CAST (appointment_datetime AS DATE)
= CAST (CURRENT_TIMESTAMP AS DATE); -- today!

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-13 : 06:11:33

i prefer sunitabeck solution over jcelko solution. it's easier to comprehend.

alanmac
Starting Member

26 Posts

Posted - 2011-08-15 : 02:55:44

quote:
Originally posted by sunitabeck

It may be simpler to use the DATEDIFF function (which would take into account the day and time - something like this)

WHERE
b.NotificationSent = 0
AND b.AppointmentDateTime BETWEEN @StartDate AND @EndDate
AND sl.NotificationPeriod = 0
AND DATEDIFF(minute, sl.NotificationTime, GETDATE()) BETWEEN -5 AND 5;

Hi Sunitabeck, thanks for the reply. Tried your code but I'm getting an error because sl.NotificationTime is in the format 15.30, so it can't convert that to an integer.

Is there a way to create a new datetime using the current date and append the NotificationTime, then do the comparison?

EDIT: Not to worry, found the answer myself by comparing the hour and minute segments.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources