In Sql 2005 to store Date / Time / Date&Time we have only two Data types:
- DateTime
- SmallDateTime
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.
Ex:
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
2 comments:
Nice start Tamil! Keep blogging.
Hi,
Its a good attempt,
I think u started this on 27th Aug(on ur B'day).Ya good starting.
i have read this,its a good job.u r explained in a simple way,keep it up....
Wish this attempt take u to greater heights.........
Best of Luck
Nithya
Post a Comment