asked on

How do I validate 15 minute intervals using SQL

I have data stored in minutes in a column called 'MYTIME'. The values can range from 1 to 1440 but I have one customer who wants to validate that that the value entered is in a 15 minute interval.
So the values 15, 30, 45, 60, 75, 90, 105 etc, etc up tp 1440 would all be valid.

Is there a simple SQL that I can run to validate the column has the right interval?

Thanks

Guy Hengel [angelIII / a3]

you have several options:
* store all the possible values in a "lookup table", and make the field a foreign key to that table
* make a check constraint that mytime % 15 = 0

knightEknight

This will pull the rows where the time is not one of those values:

; with cte as (
select 0 as interval
union all
select interval+15
from cte
where interval < 1440
)

select *
from myTable
where MYTIME not in (select interval from cte)

ASKER CERTIFIED SOLUTION

hmstechsupport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

It really depends on your definition of "validate". If you post in this zone, we assume you want a database constraint and that is what angelIII gave you here http:#a35335381. But it sounds like what you really are looking for is a presentation level validation, if so than you are correct.

Guy Hengel [angelIII / a3]

I confirm that visibly you wanted to query for those that don't match, and not yet set up a method to avoid (future) entries to confirm to the "constraint".

hmstechsupport

ASKER

Thanks to all of you, and my apologies if the question was not clear enough or posted in the wrong zone.

hmstechsupport

ASKER

The answer I was looking for was devised by me after looking in other sites.
The answers given may be accurate but they do not address my question.