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!!
我正在开发一个应用程序，它的每日报价应该存储在数据库中。每个报价都指定一年中的一天，包括2月29日的一天。由于引用只关心日期，而不关心年份，我是否还应该使用smalldatetime类型?请让我知道你的意见，谢谢!
12 个解决方案
#1
17
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 just felt a lot cleaner and much more readable.
最近我遇到了这个问题，我最初的设计确实存储了日期，我忽略了年份。然而，这感觉不太对。我决定把它删除，并有一个单独的日/月专栏代替。它给人的感觉更干净，更有可读性。
Update
更新
Long time since I wrote this answer, however, in hindsight I hold my hands up and say the comments were naively overlooked. By storing the day/month as separate fields there is the potential for storing invalid data whereas if you stored them as a full DateTime you are effectively getting that validation for free.
然而，在我写下这个答案很久之后，事后我举起双手说，这些评论被天真地忽略了。通过将日/月存储为单独的字段，就有可能存储无效数据，而如果将它们存储为完整的DateTime，则可以有效地免费获得该验证。
Depending on your validation policies this may not be a concern, however, if you rely on DB validation then I would advise you either store it as DATE and simply pull out the relevant information or use a trigger to run some validation before insert.
根据您的验证策略，这可能不是一个问题，但是，如果您依赖于DB验证，那么我建议您要么将它存储为日期，要么简单地提取相关信息，或者在插入之前使用触发器来运行一些验证。
#2
7
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.
只是一个想法。
#3
3
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.
这取决于你如何处理这些日期。如果在你的数据库中有一年不是问题，那么你可以用一个闰年，用它来存储日期，在你的应用程序视图中忽略它。
#4
2
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).
如果您需要保留日和月的数据，那么您可以使用SmallDateTime并简单地忽略year组件(或者将其设置为相同的值，例如2000年是闰年，因此允许使用leap date)。
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.
您仍然可以使用正确的数据类型使用适当的日期和时间函数，如果您使用VARCHAR字段，您将最终转换为和从它。
#5
1
Since there is no Interval type like Oracle, then you have one of a couple of choices that come to mind.
由于不存在像Oracle这样的Interval类型，所以您会想到一些选择。
- Store the year when using datetime / smalldatetime, it is going to cost you nothing extra to store it, just choose not to display it.
- 当使用datetime / smalldatetime时存储年份，存储年份不需要额外花费任何费用，只需选择不显示即可。
- Adopt a DW type approach with a date table and link to it using PK/ FK
- 采用带有日期表的DW类型方法，并使用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.
- 使用非基于日期的类型，如smallint或varchar，尽管这很可能会导致查询无法保持可sargable并避免扫描。
#6
1
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.
这还允许您随着时间的推移向数据库添加更多的引号，而不必删除已有的引号。
#7
0
You could still use a datetime column in your database and use the DatePart() SQL function to retrieve the day of the year.
您仍然可以在数据库中使用datetime列，并使用DatePart() SQL函数来检索一年中的一天。
SELECT datepart(dy,myDateColumn) FROM myTable
#8
0
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.
我将避免为此使用datetime。在某种意义上，您将存储不正确的数据。例如，你将存储4/20/2010作为4/20/2012或者任何你选择的年份。尽管年份对应用程序并不重要，但是这种方法可能会导致一些意想不到的问题。
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.
由于没有本地类型来支持您所做的工作，所以我将把值存储为varchar，并在用户定义函数中进行任何必要的计算。
#9
0
How about a varchar with 0305 being March 5.
3月5日的varchar怎么样?
#10
0
You could also consider using a single int to store a day of the year.
您还可以考虑使用一个int类型来存储一年中的一天。
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())
#11
0
I can't help feeling that if the calendar had been invented by a software engineer that the leap day would be the 32nd of December rather than the 29th of Feb. That way you could simply use a smallint offset from the 1st Jan.
我忍不住觉得，如果日历是由软件工程师发明的，那么闰日将是12月32日，而不是2月29日，这样你就可以使用1月1日的smallint偏移量。
You can still use a smallint offset from the 1st of March, with 1st March as 0, 2nd March as 1, 29th Feb as 365. However it involves doing some kind of custom conversion to the figure you want and may not sort as you would like to.
你仍然可以使用3月1日的小整数偏移，3月1日为0,3月2日为1,2月29日为365。但是，它涉及到对您想要的图形进行某种自定义转换，并且可能不按您的意愿进行排序。
Given that you can store the Day and Month as two tinyints taking up the same space, I'm not sure this would be a good plan, but thought I would mention it for completeness.
考虑到您可以将一天和一个月的时间存储为占用相同空间的两个tinyints，我不确定这是否是一个好的计划，但我认为我应该把它作为完整性的考虑。
#12
-2
CONVERT(VARCHAR(5),GETDATE(),101)
#1
17
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 just felt a lot cleaner and much more readable.
最近我遇到了这个问题，我最初的设计确实存储了日期，我忽略了年份。然而，这感觉不太对。我决定把它删除，并有一个单独的日/月专栏代替。它给人的感觉更干净，更有可读性。
Update
更新
Long time since I wrote this answer, however, in hindsight I hold my hands up and say the comments were naively overlooked. By storing the day/month as separate fields there is the potential for storing invalid data whereas if you stored them as a full DateTime you are effectively getting that validation for free.
然而，在我写下这个答案很久之后，事后我举起双手说，这些评论被天真地忽略了。通过将日/月存储为单独的字段，就有可能存储无效数据，而如果将它们存储为完整的DateTime，则可以有效地免费获得该验证。
Depending on your validation policies this may not be a concern, however, if you rely on DB validation then I would advise you either store it as DATE and simply pull out the relevant information or use a trigger to run some validation before insert.
根据您的验证策略，这可能不是一个问题，但是，如果您依赖于DB验证，那么我建议您要么将它存储为日期，要么简单地提取相关信息，或者在插入之前使用触发器来运行一些验证。
#2
7
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.
只是一个想法。
#3
3
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.
这取决于你如何处理这些日期。如果在你的数据库中有一年不是问题，那么你可以用一个闰年，用它来存储日期，在你的应用程序视图中忽略它。
#4
2
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).
如果您需要保留日和月的数据，那么您可以使用SmallDateTime并简单地忽略year组件(或者将其设置为相同的值，例如2000年是闰年，因此允许使用leap date)。
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.
您仍然可以使用正确的数据类型使用适当的日期和时间函数，如果您使用VARCHAR字段，您将最终转换为和从它。
#5
1
Since there is no Interval type like Oracle, then you have one of a couple of choices that come to mind.
由于不存在像Oracle这样的Interval类型，所以您会想到一些选择。
- Store the year when using datetime / smalldatetime, it is going to cost you nothing extra to store it, just choose not to display it.
- 当使用datetime / smalldatetime时存储年份，存储年份不需要额外花费任何费用，只需选择不显示即可。
- Adopt a DW type approach with a date table and link to it using PK/ FK
- 采用带有日期表的DW类型方法，并使用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.
- 使用非基于日期的类型，如smallint或varchar，尽管这很可能会导致查询无法保持可sargable并避免扫描。
#6
1
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.
这还允许您随着时间的推移向数据库添加更多的引号，而不必删除已有的引号。
#7
0
You could still use a datetime column in your database and use the DatePart() SQL function to retrieve the day of the year.
您仍然可以在数据库中使用datetime列，并使用DatePart() SQL函数来检索一年中的一天。
SELECT datepart(dy,myDateColumn) FROM myTable
#8
0
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.
我将避免为此使用datetime。在某种意义上，您将存储不正确的数据。例如，你将存储4/20/2010作为4/20/2012或者任何你选择的年份。尽管年份对应用程序并不重要，但是这种方法可能会导致一些意想不到的问题。
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.
由于没有本地类型来支持您所做的工作，所以我将把值存储为varchar，并在用户定义函数中进行任何必要的计算。
#9
0
How about a varchar with 0305 being March 5.
3月5日的varchar怎么样?
#10
0
You could also consider using a single int to store a day of the year.
您还可以考虑使用一个int类型来存储一年中的一天。
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())
#11
0
I can't help feeling that if the calendar had been invented by a software engineer that the leap day would be the 32nd of December rather than the 29th of Feb. That way you could simply use a smallint offset from the 1st Jan.
我忍不住觉得，如果日历是由软件工程师发明的，那么闰日将是12月32日，而不是2月29日，这样你就可以使用1月1日的smallint偏移量。
You can still use a smallint offset from the 1st of March, with 1st March as 0, 2nd March as 1, 29th Feb as 365. However it involves doing some kind of custom conversion to the figure you want and may not sort as you would like to.
你仍然可以使用3月1日的小整数偏移，3月1日为0,3月2日为1,2月29日为365。但是，它涉及到对您想要的图形进行某种自定义转换，并且可能不按您的意愿进行排序。
Given that you can store the Day and Month as two tinyints taking up the same space, I'm not sure this would be a good plan, but thought I would mention it for completeness.
考虑到您可以将一天和一个月的时间存储为占用相同空间的两个tinyints，我不确定这是否是一个好的计划，但我认为我应该把它作为完整性的考虑。
#12
-2
CONVERT(VARCHAR(5),GETDATE(),101)