Archive for March, 2010

Row Number in SQL Server 2005

In SQL Server 2005, you can use built in ROW_NUMBER() or RANK() function to generate row number for selected record set.

Syntax:

ROW_NUMBER () OVER (ORDER BY column name)

RANK() OVER (ORDER BY column name)

Example:

ROW_NUMBER ():

SELECT ROW_NUMBER()
OVER (ORDER BY applicantInformation_Name DESC) AS SerialNo,
applicantInformation_Name,
applicantInformation_Email,
applicantInformation_Phone_No
FROM t_ApplicantInformation

RANK():

SELECT RANK()
OVER (ORDER BY applicantInformation_Name DESC) AS SerialNo,
applicantInformation_Name,
applicantInformation_Email,
applicantInformation_Phone_No
FROM t_ApplicantInformation

Output:

Those two quires produce the same result.

Thanks
A Rahim Khan

Time portion of datetime data type in SQL Server 2005

To retrieve only time from a datetime data type, just convert datetime to varchar using date and time style code 108.

SELECT CONVERT(VARCHAR,GETDATE(),108)

Output:
13:43:12

However, we need to convert it to datetime data type again to accomplish any datetime operation.

SELECT DATEADD(HOUR,1,CONVERT(DATETIME,’13:43:12′,108))

Output:
1900-01-01 14:43:12:000

SELECT CONVERT(VARCHAR,
DATEADD(HOUR,1,CONVERT(DATETIME,’13:43:12′,108))
,108)

Output:
14:43:12

Thanks
A Rahim Khan