In Sql 2005 to store Date / Time / Date&Time we have only two Data types:
In 2005 we didn't any datatype to store Date or Time value alone. To overcome this issue, here we want to remove/Ignore the Time value from DateTime.
Before starting this operation, first I'd like to say that DateTime values are stored in numeric value. Execute below Query to understand it.
SELECT CONVERT (DATETIME, 0)
-- Output: 1990-01-01 00:00:00.000
–- Initial value of DateTime is 1990-01-01 (In Numeric value is "0")
-- We can increase or decrease DateTime value using Numeric values
SELECT CONVERT (DATETIME, 31)
-- Output: 1900-02-01 00:00:00.000
SELECT CONVERT (DATETIME, -31)
-- Output: 1899-12-01 00:00:00.000
SELECT CONVERT (float, getdate())
-- Displays 39685.6186604167
–- Current Date time : 2008-08-27 14:51:50.610
In the above result, 39685 (2008/08/27- 1900/01/01 = 39685 days) is value of days and .6186604167 is value of time (After decimal point values). So if we remove time values we can get the Date values alone.
SELECT CAST (FLOOR (CAST (getdate() as float) ) as datetime )
-- Displays 2008-08-27 00:00:00.000
Floor function is used to round-off float value.
Note: In SQL 2008, we have datatype to store Date or Time value separately