Wednesday, August 27, 2008

Manipulate DateTime in T-SQL - Part 1

This is my first post in this blog and I'm going to start with a simple task.

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.

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

-- Output: 1900-02-01 00:00:00.000

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