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

(2,’Dilip’,3000,’AP’),

(2,’Rahul’,5000,’DH’),

(3,’Mohan’,7000,’KR’),

(2,’Dilip’,3000,’AP’),

(2,’Rahul’,5000,’DH’),

(3,’Mohan’,7000,’KR’),

(4,’Dilip’,3000,’AP’),

(5,’Rahul’,5000,’DH’),

(6,’Mohan’,7000,’KR’),

(5,’Dilip’,3000,’AP’),

(6,’Rahul’,5000,’DH’)

Then,

Output clause

/* Local System*/

Updating

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

Deleting

delete Emp

output deleted.*

where empId=3 or empId = 6

Inserting

insert into Emp

output inserted.*

values

(13,’Dilip’,332.93,’JK’),

(14,’rakesh’,312.93,’GJ’)

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 ?

Ans:

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)

SQL SERVER – What is @@Rowcount, @@Error and @@Identity

@@Rowcount is used to display the number of rows affected by last SQL statement.

@@Error displays the error number for the last SQL statement executed. The value is zero,if there is no error.

@@identity returns the last inserted identity value.

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

SQL SEREVER – What is a View and An Indexed View

 A view  is a virtual tables that is made up of one or more tables. It can be used for security purpose, hiding complex queries.

An Indexed view is a view that has unique clustered index created on it. This takes physical storage i.e it stores data. It will be used in OLAP, decision support where inserts/updates are low. It can be used in the following scenarios

1) Joins and aggregation of big tables.

2) Repeated joins of the same tables on the same keys.

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

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 )

SQL SERVER – Default sizes for databases

Master database – Stores system files – 11MB

Model database – Template for all databases – 0.75MB

Temp database – Stores temporary objects – 8MB

Msdb database – Used by SQL server agents for scheduling alerts and jobs and recording operators- 12MB

The min. value for DB size is 512KB

The default size for DB is 1MB.

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

SQL SERVER – How many tables can SQL SERVER have and how many columns per table.

Sql server can have 2 billion tables per tables and 1024 columns per table.

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