Getting the current Time from any Time Zone

Your application might be hosted in a particular location but accessed from many location, and when user of your application ask to retrieve date specific transaction that time the time zone will be considered by default as the time zone of Hosted server so user will be getting wrong date in this case.

This is a common problem which is faced by Developer to there are many way to tackle this issue. With the advent of SQL Server 2008 and the new data type TIMEOFFSET feature, this is bit of more easy to tackle this problem. Lets see how we can arrive to a solution with the new feature of SQL Server 2008

Creating Table TimeOffsets which will store Location Name and the TimeOffset (GMT) value for corresponding location. 

create table TimeOffsets

(Location varchar(100) primary key,

TimeOffset varchar(10))

Find the insert script at the end of this article.

Now here we are creating a function which will return a table with the current time of your location and the time for asked location.

This function is taking two argument @myplace and @targetplace for which it will give the current date for @myplace location and @targetplace location. It uses

• Todatetimeoffset which will changes the current offset only  offset which we defined
• Switchoffset :It does not only change the offset. It also changes the time with the appropriate time at Time zone specified in offset. I will take both this in deeper view in coming article.

  --create the function 

create function getgeographicaltime 

(@myplace as varchar(100),@targetplace as varchar(100)

)   

returns @timeatbothplace table(yourtime datetimeoffset,targettime datetimeoffset)

as 

begin  

      declare @targetplaceoffset varchar(100)  

      declare @myplaceoffset varchar(100)     

      declare @todaysdatetime datetime2  

      declare @todaysdatetimeoffset datetimeoffset     

      declare @returndatetimeoffset datetimeoffset     

      select @todaysdatetime = getdate()     

      select @myplaceoffset =

      [timeoffset] from timeoffsets where [location] = @myplace  

      select @targetplaceoffset =

      [timeoffset] from timeoffsets where [location] = @targetplace     

      select @todaysdatetimeoffset =

      todatetimeoffset (@todaysdatetime, @myplaceoffset)     

      select @returndatetimeoffset =

      switchoffset(@todaysdatetimeoffset,@targetplaceoffset)     

      insert into @timeatbothplace(yourtime,targettime)  

      select @todaysdatetimeoffset,@returndatetimeoffset;  

      return   

end

Lets now try to call this function to see our desired result, here we are passing @myplace as ‘DELHI’ and @targetplace as ‘LAS VEGAS’. Let’s fire this query to see the result.

  

Select * from GetGeographicalTime('Delhi','Las Vegas')

YourTime                           TargetTime

---------------------------------- ----------------------------------

2013-12-18 13:11:00.3300000 +05:30 2013-12-17 23:41:00.3300000 -08:00

Here we can see we got time for LAS Vegas corresponds to DELHI time zone. So when it is 2013-12-18 13:11:00 at Delhi, Vegas evidence 2013-12-17 23:41.

INSERT Script  for TimeOffset table

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Addis Ababa', N'+03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Adelaide', N'+09:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Algiers', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Almaty', N'+06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Amsterdam', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Anchorage', N'-09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Apia', N'-11:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Asunción', N'-04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Athens', N'+02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Auckland', N'+12:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Azores', N'-01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Baghdad', N'+03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Baku', N'+04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Bangkok', N'+07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Beijing', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Berlin', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Bogotá', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Buenos Aires', N'-03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Cairo', N'+02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Calgary', N'-07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Cape Town', N'+02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Cape Verde', N'-01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Casablanca', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Chicago', N'-06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Cocos Islands', N'+06:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Colombo', N'+05:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Dakar', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Dallas', N'-06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Darwin', N'+09:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Delhi', N'+05:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Denver', N'-07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Dhaka', N'+06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Dubai', N'+04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Fernando de Noronha', N'-02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Halifax', N'-04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Hanoi', N'+07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Havana', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Helsinki', N'+02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Hong Kong', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Honolulu', N'-10:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Irkutsk', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Jakarta', N'+07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Jerusalem', N'+02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Juneau', N'-09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Karachi', N'+05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Kathmandu', N'+05:45')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Kinshasa', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Krasnoyarsk', N'+07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Kuala Lumpur', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Lagos', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Las Vegas', N'-08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Lima', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Lisbon', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'London', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Los Angeles', N'-08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Magadan', N'+11:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Maldives', N'+05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Manila', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Mauritius', N'+04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Melbourne', N'+10:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Mexico City', N'-06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Montevideo', N'-03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Montreal', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Moscow', N'+03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Mumbai', N'+05:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Nairobi', N'+03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'New York City', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Nouméa', N'+11:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Omsk', N'+06:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Pago Pago', N'-11:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Papeete', N'-10:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Paris', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Perth', N'+08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Petropavlovsk-Kamchatsky', N'+12:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Phoenix', N'-07:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Pyongyang', N'+09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Reykjavík', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Rio de Janeiro', N'-03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Rome', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Saint Petersburg', N'+03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Samara', N'+04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'San Francisco', N'-08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Santiago', N'-04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'São Paulo', N'-03:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Seoul', N'+09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'South Georgia and the South Sandwich Islands', N'-02:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Suva', N'+12:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Sydney', N'+10:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tashkent', N'+05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tbilisi', N'+04:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tehran', N'+03:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tenerife', N'+00:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tokyo', N'+09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Toronto', N'-05:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Tunis', N'+01:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Vancouver', N'-08:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Vladivostok', N'+10:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Yakutsk', N'+09:00')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Yangon', N'+06:30')

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])

VALUES (N'Yekaterinburg', N'+05:00')

Programmers jog with code and smile for no reason. Thats the beauty of programmer, love them ;) 

Post Reference: Vikram Aristocratic Elfin Share

Reference : http://blogs.msdn.com/b/manisblog/archive/2009/06/16/how-to-get-the-current-time-of-any-timezone-in-sql-server-2008.aspx

External Column Aliasing in SQL Server 2008.

Though we found inline column aliasing more practical than this new feature, still SQL 2008 provide a way out where you can alias your column externally.

Let’s see how to implement external aliasing, for demonstration I am taking two tables in action.

Creating Table Dept and Emp

CREATE TABLE [dbo].[dept](

      [dept_id] [int] IDENTITY(1,1) primary key,

      [dname] [varchar](10) NULL,

)

CREATE TABLE [dbo].[emp](

      [emp_id] [int] IDENTITY(1,1) NOT NULL,

      [ename] [varchar](10) NULL,

      [dept_id] [int] NULL

)

   

We have inserted few records in both the table

select * from dept

dept_id     dname

----------- ----------

1           software

2           HR

3           sales

(3 row(s) affected)

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           sapna      2

5           sneha      3

6           Kavita     3

7           Binny      1

(7 row(s) affected)

Now we will be querying the table to find the number of employee in each department and also will demonstrate the use of External Aliasing in it,

Query without External Aliasing (In line Aliasing)

select DepartmentID, DepartmentName,TotalEmployee from

(

select d.dept_id as DepartmentID,d.dname DepartmentName, COUNT(e.emp_id) TotalEmployee from dept d,emp e where d.dept_id=e.dept_id group by d.dept_id,d.dname

) as d

 Query with External Aliasing

select DepartmentID, DepartmentName,TotalEmployee from

(

select d.dept_id,d.dname, COUNT(e.emp_id) from dept d,emp e where d.dept_id=e.dept_id group by d.dept_id,d.dname

) as d(DepartmentID, DepartmentName,TotalEmployee)

Both will produce the same result

DepartmentID DepartmentName TotalEmployee

------------ -------------- -------------

1            software       3

2            HR             2

3            sales          2

(3 row(s) affected)

I don’t find any upper edge of this external aliasing but knowing this is interesting for discussion among your friends and showing a higher level of mastery in SQL Server. :P :D

Programmer doesn’t always roar, sometime it quiet to give a chance to his code to roar.   

Post Reference: Vikram Aristocratic Elfin Share

Special Storage Series – I (Checking the 8K (8192) size of page)

There are pages for in-row data, page for row over flow data and pages for LOB data, but in SQL Server all data pages have a fixed size of 8K (8192 bytes). They consist of:

• Page Header
• Data Rows
• Row offset Array

Page Header: It consist of first 96 bytes of each page (thus leaving 8096 byte for data rows, row over head and row offset arrays)

Data Row: This the area where the actual table data gets stored, the maximum size of singe data row is 8060 bytes of a in-row data. The rows can have row-overflow and LOB Data stored on separate page.

Row offset Array:  It is a block of 2 byte entries, indicate the offset at which the corresponding data row begins. Every row has 2 byte entries in this array. The row offset array indicates the logical order of rows on a page in case of Clustered Index, this doesn’t mean rows are physically stored in the page in the order of cluster index key.

Now we will try to check the maximum size of table structure which we can create in SQL Server 2008, as we know the maximum size of data row is 8060, we will try to create table of this length and introspect it

create table Tab_VKM_SpecialStorageCheck

(col1 char(3000),

col2 char(3000),

col3 char(2000),

col4 char(60))

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'Tab_VKM_SpecialStorageCheck' failed because the minimum row size would be 8067, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

This shows that the maximum row size values includes several bytes overheads stored with the row so the total size of the tables defined columns must be slightly less than this amount (8060 bytes)

Programmer can’t stand out without programming  

Post Reference: Vikram Aristocratic Elfin Share

Search by different parameters of stored procedure SQL Server

If you want to provide your user searching facility in such a way that he can search with any of the combination of field then to achieve this objective a stored procedure can be created, which will have parameters (all the columns of your search criteria) which could implement search in different ways

Here in this article, we have presented two ways to achieve this; you can have few more way to achieve the same.  For explanation we are creating a table Tab_VKM_Search which has five column and we will be providing user search on first_name, last_name and phone number.   

Create table Tab_VKM_Search

(

emp_id int identity(1,1) primary key,

first_name varchar(20),

last_name varchar(10),

email_id varchar(20),

phone varchar(10))

Here our table is ready, let’s insert few records in it and make a ground for our stored procedure.

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Rakesh','Sharma','rak@abo.com','982222222')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Rajesh','Mehra','raj@abo.com','982222223')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Romesh','Sippy','rom@abo.com','982222224')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Prakash','Padukone','prakesh@abo.com','982222225')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Prabhu','Chavla','pra@abo.com','982222226')

FIRST Approach (dynamic query)

Here with the help of dynamic query we are trying to provide the solution.

--PROC_Search_Employee 'pr','s'

alter procedure PROC_Search_Employee

@first_name varchar(20) = NULL,

@last_name varchar(10) = NULL,

@phone varchar(10) =NULL

as

declare @query varchar(max);

begin

      set nocount on;

      SET @query = 'select  *

              FROM Tab_VKM_Search

              WHERE 1=1 '

      IF @first_name IS NOT NULL

       SET @query = @query + ' AND first_name like '''+@first_name+'%'''

      IF @last_name IS NOT NULL

       SET @query = @query + ' AND last_name like '''+@last_name +'%''' 

      IF @phone IS NOT NULL

             SET @query = @query + ' AND phone like '''+@phone +'%'''

     

      exec(@query)

end

Second Approach (with coalesce)

Here we are using Coalesce function to come up with our solution.

--PROC_Search_Employee_II 'p','s'

alter procedure PROC_Search_Employee_II

@first_name varchar(20) = NULL,

@last_name varchar(10) = NULL,

@phone varchar(10) =NULL

as

begin

      set nocount on;

      select  * from Tab_VKM_Search  

    WHERE first_name like coalesce(@first_name + '%',first_name)

      and last_name like coalesce(@last_name + '%',last_name)

      and phone like coalesce(@phone + '%',phone)                

end

NOTE: Any of the approach you can use depends on your IO cost in the explain plan.

If you are a PROGRAMER only by CODE you build yourself...

Post Reference: Vikram Aristocratic Elfin Share