Storing Date Only in sql server 2005

Question

How to avoid the time portion of the datetime in sql server. I wants only the date portion to insert my database. Thanks in Advance

Answer 1

SQL Server Date Formats.

Answer 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.

Answer 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.

Answer 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 :)

Answer 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.

Answer 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))

Answer 7

Just represent the date as a yyyMMdd integer value.