ansaurus

Question

What is the Datatype for 10:00 AM PST in SQL Server

Answer 1

+3 A:

You could use the Time data type.

If that's overkill for you since you only need to store the interval at the level of minutes, you could store the minutes since midnight, within the range 0-1339.

You might even consider storing the number of 15-minute intervals since midnight, such that 2:00 AM is 8 and 2:15 AM is 9 (though unless you can think of a great name for such a column, it wouldn't be very clear).

Jeff Sternal 2010-05-11 19:47:04

Answer 2

+3 A:

There is a time data type you can use (SQL Server 2008).

CREATE TABLE Table1 ( Column1 time(7) )

The range is:

00:00:00.0000000 through 23:59:59.9999999

You can use a CHECK CONSTRAINT to ensure that the minutes part is one of (0, 15, 30, 45).

Oded 2010-05-11 19:47:12

Is it possible to get constant interval of 15 min if we specify the start time?

Rita 2010-05-11 19:50:08

I think this would be the better way to go. If down the road your requirements change and smaller intervals are required, you can change the constraint.

Timothy 2010-05-11 19:54:59

@Rita - it is up to you. Check Constraints are fairly flexible - you need to decide how to define them.

Oded 2010-05-11 19:58:26

@Rita, for example, `alter table table add constraint chkTableInterval check(datepart(minute, DateCreated) in (0, 15, 30, 45))`

Jeff Sternal 2010-05-11 20:02:25

Answer 3

+1 A:

If you're looking to constrain a time to 15 minute intervals, the easiest way to do it would be to just store it in an int constrained between 0-100 (some daylight savings days have 25 hours) and calculate when the exact time is if you need it. I've worked on a few large applications that have used this for stuff like weather forecasting data and it has worked quite well.

Daniel DiPaolo 2010-05-11 19:50:51

Could you please elaborate more? I need to store them as 11:00 AM PST, 11:15 AM PST, 11:30 AM PST, 11:45 PM PST, 12:00 PM PST, 12:15 PM PST, 12:30 PST, 12:45 PM PST, 01:00 PM PST..... etc Also in future if the business requirement is for 20 min interval, I should be able to change it easily.

Rita 2010-05-11 19:58:30

midnight would be `0`, 12:15 AM would be `1`, etc. The date and timezone would be stored in separate fields. Jeff has edited his response to basically copy mine.

Daniel DiPaolo 2010-05-11 20:07:01

It can often look like someone has copied your answer - unfortunately StackOverflow doesn't show the first five minutes of revision history, which I think would conclusively demonstrate that didn't happen here (you posted about four minutes after me, so there's about a minute of leeway). I didn't even read your answer until I noticed you'd accused me of plagiarizing you, and I'd appreciate it if you'd delete your accusation, in which case I'll delete this too.

Jeff Sternal 2010-05-11 22:25:00

ansaurus

tags:

views:

answers:

What is the Datatype for 10:00 AM PST in SQL Server

related questions