Get Date & Time based on time zone in SQL Server

Problem:
SQL server is running in another time zone and application is launching for different time zone, like SQL server is established in USA, and Application is lunched for Indian user so we need to maintain all data in Indian times only. So all current date and time consider Indian time rather then consider USA time zone.
Also in some case SQL server moved to USA to Canada then also system is always consider proper Indian time. Or based on user’s demanded time zone’s time.

Also in some case SQL server moved to USA to Canada then also system is always consider proper Indian time. Or based on user’s demanded time zone’s time.

Solution:
So below are the sql function for that.

GO
/****** Object:  UserDefinedFunction [dbo].[GetLocalDateTime]    Script Date: 02/10/2012 15:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Created By : Amit Patel
Created Date : 11-Feb-2012
*/
------------------------------How to Executes--------------------------------------------------
-- select [dbo].[TimeZoneWiseTime](+5)
CREATE FUNCTION [dbo].[GetDateTimeByTimeZone]
(
	@TimeZoneDiff decimal(4,2)
)
RETURNS datetime
AS
BEGIN
	declare @utcdiff decimal(5,2)
set @utcdiff =  datediff(hh,GetUtcDate(), getdate())
declare @currDate datetime
set @currDate = DATEADD(Hour,@utcdiff,getdate())

return DATEADD(Hour,-(@TimeZoneDiff),@currDate )
END

In this example I have give example of select [dbo].[TimeZoneWiseTime](+5)
to consider Indian standard time (IST) as +5. So we can set it to take any country’s time.

In above logic I am first considering GetUtcDate() to get central time and based on that find difference of server’s date with central time, and finally after all I am adding uses requested timezone’s hours.

Let me know if you have any questions or feedback on this.

Thanks,
Amit Patel
Enjoy Programing

About these ads
Follow

Get every new post delivered to your Inbox.

Join 239 other followers