How to convert Server time into Localtime in sql server 2005

You should use the DATETIMEOFFSET datatype which includes the timezone, and the SWITCHOFFSET method to switch between timezones. Also: to get the current time, use SYSDATETIMEOFFSET() instead of GETDATE()

-- gets current date/time in the current timezone

SELECT 

SYSDATETIMEOFFSET()

-- get the current date/time in your preferred timezone +05:30 UTC being Indian Std. Time

SELECT 

SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')