Using something other than datetime for dates and times
-
I've been working with SQL Server for many years and always have used the datetime (and smalldatetime) built-in types. Like most folks I've occasionally wished for separate datatypes to capture date and time (I understand 2005 was going to have one for time only, but it's been yanked?).
Now I'm managing an application that a team in another location is actually coding. I'm doing the db design, but with a lot of input from them, as they have some experience already in doing this kind of application. They prefer to use char(8) for date (stored in "yyyymmdd"
I would very much like to hear about other folks' experience using custom types for date and time. Are we about to drive off the cliff? If custom times are OK, then what about smallint vs. char(4) for time?
-
Personally I'd say datetime values should always be stored as datetime values. The ease and variety of ways you can split the date portion form the time portion almost makes the case for seperate storage a moot point. In terms of database storage the 8 bytes for datetime beats the 12 bytes for the 2 char fields.
Storing them as char values, how do you perform the many date comparissions? How many days since the last order? How many hours elasped between the helpdesk call being raised and when the issue was resolved, etc..., etc..., etc...
The only way you're going to perform these sort of calculations is to convert the value to a datetime anyway.
--------------------
Colt 45 - the original point and click interface
-
If you don't invest in determining the correct datatype for you attributes/columns, don't even wast time at performance !
Indeed,with sqlserver you only have a (small)datetime, and that has it cons, but live with it and use it !
If you still want to split date and time, document it so u use fixed in the "unused" part, and have it checked by a constraint.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I would tend to stick with the built in DATETIME/SMALLDATETIME data types.
If you must use SMALLINT for times then I don't see the problem with parsing it.
For CHAR(4) your programmers use LEFT(column,2) for the hours and RIGHT(column,2) for Minutes.
For SMALLINT your progammers use FLOOR(column/100) for the hours and column % 100 for minutes.
You would save 2 bytes in storage space each record but you would have to make sure that whatever looks after the integrity of the column was robust.
-
I don't have any experience with custom types for dates but have in one instance been using datetime to store "just time" and its been working fine.
I have to ask why is a non datetime data type being considered? What is it that you expect to gain?
-
Presumably, the only 'gain' one may see is easier to understand comparison operators (e.g. ,...) If one compares two datetime instances with '<' the difference is resolved at a higher resolution than date alone (assuming the data are present). This can lead to code which is syntactically correct, and semantically wrong, but which also 'reads' properly. I've seen this happen in Java code whose Date class has similar external structure to datetime. I've stared down the comparison code and not had it dawn on me that millisecond differences might render the 2 instances 'different'.
As far as the difficulty parsing is concerned, if they can't parse it from a smallint, they have no business writing software anyway. In fact one could argue that smallint is preferable since it is vastly easier to maintain code which uses a centralized getTime(smallint) call vs. hundreds of left(...) right(...) calls scattered throughout the codebase (and people will do that if they know they can).
I'd say that the factor which tips the scales is reporting. If one sticks to the built in types, the report writers can deal with dates much more easily than they can with a new wheel.
If they feel that the application side will really benefit from making a distinction between date and time, there is nothing preventing them from splitting the 2 components out on their end (just bear in mind the cost of redoing all of those date utility functions)
-
Hello every body in list, here you have an example of how to return the date from a datetime format string..... ProdDateAdd is in DateTime Format.
Solution.....
SET DATEFORMAT DMY
select CONVERT(char(10), ProdDateAdd, 103) as ProductDateAdded from TblProducts
I hope you'll enjoy it.
Regards BelerofonteCU. SQL DBA.
-
In cases where the time portion really did not matter I have used datetime with a constraint to ensure all of the time values were zero. That way I retain all the benefit of the datetime type and removed all the problems of date comparison.
-
If you are going to use custom types, while you are at it, make your date part an int field to save 4 bytes of storage there as well.
I have to agree that it would be better to use the datetime fields for storing this type of data, just because of the extensive programming support this data type has built into it.
On the other hand, the native SQL support for just date and just time formats really sucks. It can be done, you can build functions to do it, but it is NOT an easy conversion process.
"Easier to parse"? So, how many days of programming experience does your team have?
-
It's not that hard to do for the time part... but I wouldn't want to code the functions to handle the date part :
Declare @ms as int
Set @ms = DateDiff(MS, dateadd(D, 0, DateDiff(D, 0, GetDate())), GetDate())
Select GetDate() as Now, @ms as Milliseconds, @ms / 3600000 as Hours, @ms / 60000 % 60 as minutes , @ms / 1000 % 60 as seconds , @ms % 1000 as MS
Now Milliseconds Hours minutes seconds MS
------------------------------------------------------ ------------ ----------- ----------- ----------- -----------
2005-05-31 15:40:47.163 56447163 15 40 47 163
-
Thanks for all the responses, guys!
I am feeling pretty queasy now about letting them have their way with char(6) instead of smalldatetime.
[Edit] It's just so easy to set the time portion to 0, of course [Edit]
For time only fields, I'm not so sure. This particular database has a lot of places where time-only is needed. For example, an employee can only work between 1000 and 1800, so we have that in a schedule configuration table. That kind of thing. Of course best thing would be for SQL Server to have a time-only datatype, but since it doesn't....we are left with the choice of using smalldatetime with date set to Jan 1, 1900 and always parse out the time (ugh!!), or going with custom type, with candidates being char(4) or smallint. In this case, I can really see the case for using something different than smalldatetime.
Perhaps what they meant by char(4) vs smallint being easier to parse is that with char(4) you can take right two characters and then convert to integer and validate >= 0 and <= 59, for example. But when user enters the time, it's going to be a string anyway. Once it's validated, it can go into the database as smallint. I think I'd have to really spend some time doing some programming with it to see which one I really preferred.
Jim
-
No big deal getting the time from a datetime or smalldatetime.
select Right(convert(varchar,getdate()),7) as Time
Time
-------
8:50PM
-
Isn't parsing and validating something that's done on the client, or business layer? What is the client application developed in? Does it understand native date and time datatypes?
--------------------
Colt 45 - the original point and click interface -
.NET (C#)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply