Best method to store time

Question

Best method to store time

megan_c99

SSC Rookie

Points: 28
More actions
May 16, 2012 at 11:59 am

#261360

Hi,
I'm working on a project that requires us to store an employee's work schedule.
For example our company allows the following work schedules:
8:00 a.m. to 5:00 p.m.
8:30 a.m. to 5:30 p.m.
However, some employees could have other times too e.g. 7:30 a.m. to 4:00 p.m. This will be entered by the employee in a text box. with a AM/PM dropdownlist.
Question: What would be the best way to store the other times? Use Datetime or varchar?
Any help is much appreciated.
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

megan_c99 (5/16/2012)
Hi,
I'm working on a project that requires us to store an employee's work schedule.
For example our company allows the following work schedules:
8:00 a.m. to 5:00 p.m.
8:30 a.m. to 5:30 p.m.
However, some employees could have other times too e.g. 7:30 a.m. to 4:00 p.m. This will be entered by the employee in a text box. with a AM/PM dropdownlist.
Question: What would be the best way to store the other times? Use Datetime or varchar?
Any help is much appreciated.
Thank you.

SQL Server 2008 also hase a TIME data type, and that is what I would use.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

What about an employee on rotational shifts or flexible timing? Or the one who do overtime? Time data type wouldn’t be sufficient to hold that information.

megan_c99 SSC Rookie Points: 28 More actions · Answer 3

Thanks for the prompt response.

The idea is that employee's pick one schedule and stick to it. So for example if an employee works from 7:30 a.m. to 4:00 p.m. it's expected that he/she works the same hours everyday. Company just wants to know what time the employee is available everyday as an "FYI".

So, use Datetime instead?

Thank you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'd still use the TIME data type. If you do have hours that go from 20:00 to 05:00, I would deal with that in code. If all you are doing is displaying a start time and end time, this makes it easier and you don't have to worry about an extranous date part.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

I love solving problems with handling time.

Daylight savings?

Multi-national? then dont forget different countries and timezones.