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’


–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’


— Atlantic Time = UTC-4

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


— Eastern Time = UTC-5

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


— Mountain Time = UTC-6

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


— Pacific Time = UTC-8

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


— Alaska Time = UTC-9

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


— Hawai Time = UTC-10

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


Display all time Zones

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

About SqlExplore – Year 2012 in Review – Overview of Blog

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 44,000 views in 2012. If each view were a film, this blog would power 10 Film Festivals

Click here to see the complete report.


SQL SERVER – How to work with Row Constructors , inserted and deleted tables and Handle Result Set

There are many ways to track changes made to the table in SQL Server one of the method is to use inserted and deleted tables and get the result set and use in code appropriately.

Step 1: Creating Table

create table Emp


empId int primary key identity,

class int,

empName varchar(30),

sal money,

empState varchar(25)


Step 2: Inserting values into the table

use row constructors or insert one by one

insert into Emp values













Output clause

/* Local System*/


update Emp Set sal = sal +1

output inserted.empId,deleted.sal as [old sal], inserted.sal as [new sal]

where class =2

/* table Variable for server side maintenance*/

declare @change table


id int not null,

[old sal] money,

[new sal] money


update Emp Set sal = sal +1

output inserted.empId,deleted.sal as [old sal], inserted.sal as [new sal] into @change

where class =2

select * from @change


delete Emp

output deleted.*

where empId=3 or empId = 6


insert into Emp

output inserted.*




For Updating,deleting and inserting we get a result set.

Update 21 Jan 2013

One of my reader Mini asked me that she is trying to insert multiple values into table using row constructor but it is giving error in the comma in the syntax ?


1)  Result set are not supported in sql server expressed Edition

2) Inserting multiple rows or technically can be called as Row Constructors are new feature added in SQL Server 2008

if we try to execute the above code on SQL Server 2005 we will get an error at comma stating that it is not allowed, because this syntax is introduced in SQL Server 2008 and even when we are working on client server environment no matter what SQL Server you have if the server to whom you are connecting is previous versions of SQL Server 2008 it will not execute, so these things should kept into consideration.

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

We have a table with a CHARACTER data type field. We apply a “>” row comparison between this field and another CHARACTER field in another table. What will be the results for records with field value of NULL

This is something  so many asked me because I also faced the same situation before, so having a NULL in a row when compared will give an UNKNOWN result.

The result will not be correct hence creates an ambiguity.

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

SQL SERVER – What is the basic difference between a join and a union

A join selects columns from 2 or more tables. A union selects rows.

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

SQL SERVER – CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

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

SQL SERVER – what are the DBCC commands that are commonly use for database maintenance

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

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