select rows by adding to datetime

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

SQL Server 2008 Forums

Transact-SQL (2008)

select rows by adding to datetime

Author

Topic

jwigg5pt0
Starting Member

8 Posts

Posted - 2012-02-06 : 16:15:05

I have a table for appointments with columns consisting of id(unique int), duration(int), startDT(datetime, endDT(datetime).

The duration is setup so every 15 min increment is equal to 1(how I am reading on the front end application for certain functions:
1 = 15min
2 = 30min
3 = 45min
4 = 1hour
5 = 1hour & 15min
.......
I need to check the endDT against the combination of the duration and startDT by finding rows that do not match properly.
What I'm trying to accomplish is to add whatever the duration is to the startDT and make sure that it is the corrent endDT.
I have used a function in .net that using division and remainder to find how many hours and min to add to the startDT to get the endDT.
Example:
int hour = dur / 4;
int r = dur % 4;
int min = 0;
switch (r)
{
case 0: min = 0; break;
case 1: min = 15; break;
case 2: min = 30; break;
case 3: min = 45; break;
}
endDT = startDT.AddHours(hour);
endDT = startDT.AddMinutes(min);
Is there a way to do this is sql to just select the rows that don't have the corrent endDT while using a function like this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:58:00

do you mean this?


SELECT  id, duration, startDT, endDT 
FROM table
WHERE DATEADD(minute,duration * 15 ,startDT) <> endDT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwigg5pt0
Starting Member

8 Posts

Posted - 2012-02-06 : 19:21:28

exactly perfect

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 20:26:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources