April 10, 2006 at 3:51 am
Hi all,
I have a database table where I will need to store entries that will be logged at different intervals throughout the day, and am wondering what is the best way to store this information.
Is it best to just have one DATETIME column and to store the dates and times in here by constructing the date and time from the user entered data, or is it best to have separate columns, one for date and one for time, where data entered on the same day will have the same date data whilst the times will obviously be different?
Thanks in advance.
Tryst
April 10, 2006 at 4:28 am
Since there isn't a separate "time" data type (the time column without the date would be irrelevant anyway) I'd just have the one datetime column and then query using "convert" & the various date functions to filter the result sets...
**ASCII stupid question, get a stupid ANSI !!!**
April 10, 2006 at 5:35 am
Having had to work with times and dates many times, I recommend that you use a datetime field.
What data type would you use for the time portion?
If you use a character field, there is no foolproof way to compare two values stored in this manner without a lot of string parsing.
If you choose a numeric or integer type, and use a 24 hour time format, the same situations apply. You would need to write a validation routine for any user entires. What time is 1279? How about 30000?
Lastly, if you have to do any caculations with times, the conversions from a decimal numbers to time can be innacurate.
For example, using an employee time system, if an employee works 20 minutes today (which is 33% (or 1/3) of an hour), tomorrow, and the next day, his total time worked would be 99% of an hour.
Stick with the datetime.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 10, 2006 at 6:38 am
Cool! Excellent replies people!
Some good insights there, Michael.
Thanks
Tryst
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply