存储日期仅在SQL Server 2005中

How do I avoid storing the time portion of a datetime in SQL Server, i.e. if I have a value of 2011-01-01 00:00:00.000 I want to store only 2011-01-01?

如何避免在SQL Server中存储日期时间的时间部分,即如果我的值为2011-01-01 00:00:00.000我想仅存储2011-01-01?

I want to ensure that only the date portion is stored.

我想确保只存储日期部分。

7 个解决方案

#1

SQL Server Date Formats.

SQL Server日期格式。

#2

The DateTime data type ALWAYS stores the date AND time. So you are left with using CONVERT/CAST to obtain a particular format, or use the YEAR(), MONTH() or DAY() methods to isolate date details depending on your need.

DateTime数据类型始终存储日期和时间。因此,您只需使用CONVERT / CAST来获取特定格式,或使用YEAR(),MONTH()或DAY()方法根据您的需要隔离日期详细信息。

#3

The easiest solution is just to not expose the time portion to the user. However, if you really need to make sure only the date part is stored, you could force the time portion to midnight/midday/any constant time before storing the value.

最简单的解决方案是不将时间部分暴露给用户。但是,如果您确实需要确保仅存储日期部分,则可以在存储值之前将时间部分强制为午夜/中午/任何固定时间。

#4

The built-in DATETIME data type stores both the date and time data. If you specify only the date portion then the time will be 12:00:00 or something like that.

内置的DATETIME数据类型存储日期和时间数据。如果您只指定日期部分,那么时间将是12:00:00或类似的时间。

Funny story: I saw a database once where there was a date and a time field, both stored the date and the time, but each was used only for half of the data. Some people do silly things :)

有趣的故事:我看到一个数据库曾经有一个日期和时间字段,都存储了日期和时间,但每个只用于一半的数据。有些人做傻事:)

#5

If you cast a DateTime to an Int and back you will get a DateTime with 00:00 as the time part. So you could save all your dates as integers in the database.

如果将DateTime转换为Int并返回,则将获得带有00:00的DateTime作为时间部分。因此,您可以将所有日期保存为数据库中的整数。

#6

Either add a computed column:

添加计算列:

dateonly AS CONVERT(DATETIME, CONVERT(CHAR(8), date_with_time, 112), 112)

or truncate your date right on insert:

或者在插入时截断您的日期:

INSERT
INTO     mytable (dateonly)
VALUES   CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112), 112)

, making a CHECK on your dateonly column to raise an error when someone tries to insert a non-truncated value:

,当有人试图插入非截断值时,对您的dateonly列进行CHECK以引发错误:

CHECK (dateonly = CONVERT(DATETIME, CONVERT(CHAR(8), date_with_time, 112), 112))

#7

Just represent the date as a yyyMMdd integer value.

只需将日期表示为yyyMMdd整数值即可。

#1