Tuesday, September 9, 2008

Microsoft Visual Studio 2008 Service Pack 1 Offline Installer

Hi all,

Whoever don't have internet connection and want to install Visual Studio 2008 SP1.
You can download Complete Service Pack 1 (832 MB) Installer in this link. It doesn't requires internet connection while Installing.


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.

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