blocks|key|365885|text|我最近遇到了这个问题，我最初的设计确实存储了日期，但我只是忽略了年份。然而，它就是感觉不对劲。我决定删除它，改为使用一个单独的Day/Month列。它只是感觉干净多了，可读性也好多了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|365886|更新|BOLD|365887|然而，在我写这个答案很长一段时间后，事后我举起我的手，说这些评论被天真地忽视了。通过将日/月存储为单独的字段，可能会存储无效数据，而如果将它们存储为完整的DateTime，则可以有效地免费获得验证。|365888|根据您的验证策略，这可能不是一个问题，但是，如果您依赖于DB验证，那么我建议您将其存储为DATE并简单地提取相关信息，或者在插入之前使用触发器运行一些验证。|365889|entityMap^0|1R|3|1V|5|0|0|2|0|25|8|0|18|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@$9|U|A|V|B|H]]|D|@]|E|$]]|$1|I|3|J|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|K|3|L|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$]]

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 <code>Day</code>/<code>Month</code> 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 <code>DateTime</code> you are effectively getting that validation for free.

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 <code>DATE</code> and simply pull out the relevant information or use a trigger to run some validation before insert.

blocks|key|365899|text|另一种选择(我不认为其他人会提供)是将月份和日期存储为单独的整数。因此，要查找今天的条目，您可以：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|365900|select+quote+from+quoteTable+where+month+=+4+and+day+=+20;|code-block|syntax|javascript|365901|这将允许您在不使用日期(并忽略年份)的情况下获得特定于日期的消息。|365902|这只是个想法。|365903|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>select quote from quoteTable where month = 4 and day = 20;
</code></pre>

This would allow you to have day specific messages without using dates (and ignoring the year).

Just an idea.

blocks|key|1692541|text|这取决于你必须如何处理这些日期。如果你的数据库中有年份不是问题，那么你可以选择一个闰年，并用它来存储日期，在你的应用程序视图中忽略它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1692542|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3101095|text|如果您需要保留日期和月份数据，那么您不妨使用SmallDateTime并简单地忽略year组件(或者将其全面设置为相同的值，例如，2000年是闰年，因此允许使用闰年)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3101096|您仍然可以使用具有正确数据类型的适当日期和时间函数，并且如果使用VARCHAR字段，则最终将在它之间进行转换。|3101097|entityMap^0|M|D|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

If you need to retain the day and month data, you might as well use <code>SmallDateTime</code> 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.

blocks|key|3101389|text|我不禁觉得，如果日历是由软件工程师发明的，那么闰日应该是12月31日而不是2月29日，这样你就可以简单地使用从1月1日开始的小偏移量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3101390|您仍然可以使用3月1日的小偏移量，3月1日为0，3月2日为1，2月29日为365。然而，它涉及到对您想要的图形进行某种自定义转换，并且可能不会按您希望的方式排序。|3101391|考虑到您可以将Day和Month存储为占用相同空间的两个小数字，我不确定这是否是一个好计划，但为了完整性，我想我会提到它。|3101392|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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. 

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.

blocks|key|1692621|text|由于没有像Oracle这样的间隔类型，因此您可以从脑海中想到两个选择之一。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1692622|1692623|存储年份使用datetime+/+smalldatetime时，存储它不会产生任何额外成本，只需选择不显示它即可。|unordered-list-item|1692624|采用带有日期表的DW类型方法，并使用PK/+FK|1692625|链接到该DW表使用不基于日期的类型，如smallint或varchar，尽管这可能会导致使查询保持可保存并避免扫描的一些困难。|1692626|1692627|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|N|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|P|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|Q|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|L|$]]

Since there is no Interval type like Oracle, then you have one of a couple of choices that come to mind.

<ul>
<li>Store the year when using datetime /
smalldatetime, it is going to cost
you nothing extra to store it, just
choose not to display it.</li>
<li>Adopt a DW type approach with a date
table and link to it using PK/ FK</li>
<li>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.</li>
</ul>

blocks|key|1110538|text|直接的流水号怎么样。您可以每次随机选择引号，并在选择时标记另一个布尔值字段。您可以在年底重置布尔型字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110539|这还允许您随着时间的推移向数据库添加更多的报价，而不必删除已有的报价。|1110540|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1692661|text|您仍然可以在数据库中使用datetime列，并使用DatePart()+SQL函数来检索一年中的某一天。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1692662|SELECT+datepart(dy,myDateColumn)+FROM+myTable|code-block|syntax|javascript|1692663|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could still use a datetime column in your database and use the DatePart() SQL function to retrieve the day of the year.

<pre><code>SELECT datepart(dy,myDateColumn) FROM myTable
</code></pre>

blocks|key|1114154|text|为此，我会避免使用datetime。从某种意义上说，你会存储不正确的数据。例如，您可以将2010年4月20日存储为2012年4月20日或您选择的任何年份。尽管年份对您的应用程序无关紧要，但这种方法可能会导致一些意想不到的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114155|例如，如果你以某种方式获得了错误年份的日期，该怎么办？你的计算会错的。|1114156|因为没有本机类型来支持您正在做的事情，所以我会将值存储为varchar，并在用户定义的函数中执行任何必要的计算。|1114157|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|365606|text|0305为3月5日的varchar如何？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|365607|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

How about a varchar with 0305 being March 5.

blocks|key|1692829|text|您还可以考虑使用单个int来存储一年中的某一天。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1692830|在人类可读的格式和一年中的某一天之间进行转换可能有点麻烦。另一方面，将日期分配给引号并选择它们将非常容易。|1692831|SELECT+quote+FROM+QuoteTable+
WHERE+dayOfYear+=+DATEPART(dy,+GETDATE())|code-block|syntax|javascript|1692832|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>SELECT quote FROM QuoteTable 
WHERE dayOfYear = DATEPART(dy, GETDATE())
</code></pre>

blocks|key|365951|text|CONVERT(VARCHAR(5),GETDATE(),101)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|365952|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CONVERT(VARCHAR(5),GETDATE(),101)
</code></pre>

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!!

What is the best way to store a date without a year in SQL database?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在构建一个应用程序，有一个每日报价，应该存储在数据库中。每个报价都分配了一年中的一天，包括2月29日。既然引用只关心日期，而不是年份，我应该仍然使用smalldatetime类型吗？请让我知道你的意见，谢谢！

问在SQL数据库中存储没有年份的日期的最好方法是什么？
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL数据库中存储没有年份的日期的最好方法是什么？EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL数据库中存储没有年份的日期的最好方法是什么？
EN