Storing the date Only in 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?

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

回答1:

SQL Server Date Formats.

回答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.

回答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.

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.

回答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:

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

回答7:

Just represent the date as a yyyMMdd integer value.