SQL Server 2008 – All Time Zones – UTC – IST – Atlantic Time – Eastern Time – Mountain Time – Pacific Time – Alaska Time – Hawai Time

Often we encounter problem in converting and storing the correct date time in databases. The major problem comes when we have a web site for specific country or open to all the world and our Server location is somewhere else and storing the data according to the user or client location becomes very difficult.

Below are some most commonly used Time Zones which can be made use of so that it can be kept in the logic while storing the data correctly according to the time zone.

–UTC Time

SELECT ‘UTC Time’ as ‘TimeZone’,’UTC’ as ‘TimeAbb’ ,CONVERT(varchar(20),GETUTCDATE(),113) as ‘Current Time’

UNION ALL

–IST Time i.e. India Time=UTC+5:30

SELECT ‘India Time’ as ‘TimeZone’,’IST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(MI,30,DATEADD(hh,5,GETUTCDATE())),113) as ‘Current Time’

UNION ALL

— Atlantic Time = UTC-4

SELECT ‘Atlantic Time’ as ‘TimeZone’,’ATS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-4,GETUTCDATE()),113) as ‘Current Time’

UNION ALL

— Eastern Time = UTC-5

SELECT ‘Eastern Time’ as ‘TimeZone’,’ETS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-5,GETUTCDATE()),113) as ‘Current Time’

UNION ALL

— Mountain Time = UTC-6

SELECT ‘Mountain Time’ as ‘TimeZone’,’MTS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-6,GETUTCDATE()),113) as ‘Current Time’

UNION ALL

— Pacific Time = UTC-8

SELECT ‘Pacific Time’ as ‘TimeZone’,’PST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-8,GETUTCDATE()),113) as ‘Current Time’

UNION ALL

— Alaska Time = UTC-9

SELECT ‘Alaska Time’ as ‘TimeZone’,’AKST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-9,GETUTCDATE()),113) as ‘Current Time’

UNION ALL

— Hawai Time = UTC-10

SELECT ‘Hawai Time’ as ‘TimeZone’,’HST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-10,GETUTCDATE()),113) as ‘Current Time’

Output

Display all time Zones

Reference : Dilip Kumar Jena (https://sqlexplore.wordpress.com)

Advertisements

SQL SERVER 2008 – What is the difference betweeen Execute and sp_executesql

sp_executesql and Execute statement are used to execute a sql stored Procedure. The execute statement doesn’t support parameter substitution in the executed string.

According to Sql server Book Online Link Here.

‘To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesqlis more efficient than EXECUTE.’

So the best thing to use is sp_executesql but depends according to the requirement.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )