I am building an app that has a daily quote that should be stored in the database. Each quote is assigned with a day of the year, including one for Feb 29th. Since the quote only cares about the day not the year should I still use smalldatetime type? Please let me know your opinions, thanks!!
+3
A:
It depends on what you have to do with those dates. If having the year in your db is not a problem then you can take a leap year and use that for storing dates, ignoring it in your app view.
mamoo
2010-04-20 12:34:04
I wouldn't do this. You're storing incorrect data in your database. It could lead to unexpected problems.
John M Gant
2010-04-20 12:44:47
@jmgant: nothing could lead to unexpected problems (!) :P, but seriously - he is not storing incorrect data, but meaningless data; these few bits are used to validate the domain of the data so that you don't have to have complex check constraint, like you would with day and month fields and the variations of such.
Unreason
2010-04-20 13:39:55
@Unreason: I would argue that it is incorrect. A datetime represents a specific instance in time. Using that data type to store an annually recurring month and day, which is a different type of data, is just wrong IMO. Besides, having already decided that February 29 is always acceptable, and since we're not including times or non-Western calendars, the validation just isn't that difficult.
John M Gant
2010-04-20 14:35:18
@jmgant, it is us who give semantics to the stored bits. Yes, it can be misleading. Yes, if there was a domain more appropriate it would be better. But, no, it is not simply wrong - not more than saying that decimal(2,1) represent points on a line and storing only a known subset of the domain (for example ratios x/2 or x/4) in it is just wrong (analogy is not great, but hopefully point does get accross). It could be that it would be better to split these into two columns, but in case you are interested only in decimal representation of the ratio, it is not. :)
Unreason
2010-04-20 20:18:50
+2
A:
If you need to retain the day and month data, you might as well use SmallDateTime and simply ignore the year component (or set it to the same value across the board, for example 2000 which was a leap year, so leap dates will be allowed).
You still get to use proper date and time functions with the correct data type and if you go with a VARCHAR field you will end up converting to and from it.
Oded
2010-04-20 12:34:41
+1
A:
Since there is no Interval type like Oracle, then you have one of a couple of choices that come to mind.
- Store the year when using datetime / smalldatetime, it is going to cost you nothing extra to store it, just choose not to display it.
- Adopt a DW type approach with a date table and link to it using PK/ FK
- Use a non date based type such as smallint or varchar, although this may well result in some difficulties in getting queries to remain sargable and avoid scans.
Andrew
2010-04-20 12:36:43
A:
You could still use a datetime column in your database and use the DatePart() SQL function to retrieve the day of the year.
SELECT datepart(dy,myDateColumn) FROM myTable
ChrisNel52
2010-04-20 12:39:34
A:
I would avoid using a datetime for this. In a sense, you'd be storing incorrect data. For example, you'd be storing 4/20/2010 as 4/20/2012 or whatever year you chose. Even though the year doesn't matter to your application, this approach could lead to some unexpected problems.
For example, what if you somehow got a date in there with the wrong year? Your calculations would be wrong.
Since there's no native type to support what you're doing, I would store the values as varchar and do any necessary calculations in a user-defined function.
John M Gant
2010-04-20 12:40:45
+10
A:
I had this problem recently, my initial design did store the date and I just ignored the year. However, it just didn't feel right. I decided to just remove it and have a separate Day/Month column instead. It is a lot cleaner and much more readable.
James
2010-04-20 12:42:57
I would not say that it would be any cleaner if you had to do any day based calculations on the data afterwards.
Unreason
2010-04-20 13:32:56
@Unreason: It is cleaner from a DB point of view. Storing the full date IMO is wrong. You should only store information you *need*.
James
2010-04-20 13:41:28
@James: In case you are not doing proper CHECKS on your day/month then your solution only seems cleaner. By using date domain you have those checks for free. As for storing only information you need - we are not deviating so much there (year has no meaning, it is not information); the situation is similar to wasting few bits from let's say decimal(4,4) type. The benefit is that you get quick and good domain check.
Unreason
2010-04-20 13:57:20
@Unreason: Yes, however, if you are verifying the date as `20/04/2010` when in fact the date you are really referring to is `20/04/2011` then your built-in checks aren't providing you any advantages they are simply verifying that `20/04` was a valid date in 2010 which is no use to you, it has no relation to the *real* year you want. For me the date would have to be checked on the application side before it is persisted to the database anyway.
James
2010-04-20 14:15:13
@James I think Unreason is more concerned about trying to store 31/04 and having the DB pick up that error. By using a datetime column you get that sort of check for free. While you sort of address this with doing client side validation, it does open you up to the DB being able to contain data that is invalid. With a datetime column the DB *can't* store invalid data. Alternatively you could probably fake the check by having an insert trigger that protects against invalid day/month combinations - but I'd rather the DB did DB stuff for me than manually emulate DB functionality.
Peter M
2010-04-20 15:33:40
@James: relying on application level to check integrity is a bigger offense in my modeling book compared to choosing a domain that is slightly over-dimensioned :) As for complexity of the table: it needs to check that month is between 1 and 12 and that for months 1,3,5,7,8,10,12 the day is between 1 and 31 and for months 4,6,9,11 is between 1 and 30 and for month 2 is between 1 and 29, so, yes it is not so complicated, but still, I would not call one solution a lot cleaner then the other. On the other hand, storing it as dates will force users/applications to provide the year...
Unreason
2010-04-20 15:45:16
...which is meaningless (unless you create VIEWS and update with INSTED-OF triggers, or do all your application side updates through stored procedures).
Unreason
2010-04-20 15:48:33
@Unreason: By cleaner I was referring to how it was on the DB end of things i.e. if you had a column called `YearEnd` for example and it stored a datetime (bare in mind year end will change EVERY year) it is incorrect. Where as if you had 2 columns one called `YearEndDay`/`YearEndMonth`, for me, that is cleaner and makes more sense.
James
2010-04-20 19:44:57
@James. Well, for me there is no much difference between the two solutions (and for the example you just quoted, if I get it - that one is a real date, so splitting it to day and month really cripples you in term of date operations on it, indexing it, etc). But still, this is a physical implementation detail and YMMV depending on how you use the data.
Unreason
2010-04-20 20:10:40
+6
A:
Another option (I don't think anyone else has offered) would be to store the month and day as separate ints. So, to find todays entry, you could:
select quote from quoteTable where month = 4 and day = 20;
This would allow you to have day specific messages without using dates (and ignoring the year).
Just an idea.
Todd R
2010-04-20 12:43:40
+1
A:
How about a straight running number. You could choose the quotes at random each time and mark another boolean field as they are chosen. You can reset the boolean field at the end of the year.
This also allows you to add more quotes to the database as time goes without having to delete the ones you already have.
galford13x
2010-04-20 12:50:45
A:
You could also consider using a single int to store a day of the year.
It could be a little bit painful to translate between human-readble format and day-of-the-year. On the other hand, it will be very easy to assign the dates to the quotes and to select them.
SELECT quote FROM QuoteTable
WHERE dayOfYear = DATEPART(dy, GETDATE())
VladV
2010-04-20 13:34:02