Aristocratic Elfin Share: DATETIME and DATETIME2

Showing posts with label DATETIME and DATETIME2. Show all posts

Tuesday, June 24, 2014

Listing Next 5 Sunday

Again I got an opportunity to write an interesting piece of College Code in response to the question posted on one of my SQL Server forum. The problem statement was

“I want a list of next five Sunday, how I can achieve this through T-SQL”

Ya its very simple, just a couple of Date and String function, blend it with college logic, have your tea with Parle G and that’s all code will all set to fly high. Enjoy Sunday J

So here I am sharing a piece of code which will do the rest, the code is self explanatory, So I don’t think I should buy my time to catch up anything from code to explain.

declare @dt datetime

declare @howMany int

declare @cntr int

set @cntr = 0

set @howMany = 6

set @dt = getdate()

create table #temp

(

SundayCol datetime,

WeekDayName varchar(10)

)

while (datename(w,@dt) not like 'Sunday')

begin

set @dt= dateadd(dd,1,@dt)

end

--select datename(w,@dt)

insert into #temp

select @dt, Datename(w,@dt)

while (@cntr < @howMany)

begin

set @dt = dateadd(dd,7,@dt)

insert into #temp

select @dt, Datename(w,@dt)

set @cntr = @cntr + 1

end

select convert(varchar(10),SundayCol,103), WeekDayName from #temp

drop table #temp

Execute it and output you will get is next 6 Sunday. Enjoy your Sunday J

Date WeekDayName

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

29/06/2014 Sunday

06/07/2014 Sunday

13/07/2014 Sunday

20/07/2014 Sunday

27/07/2014 Sunday

03/08/2014 Sunday

10/08/2014 Sunday

The popping up of Sunday every week makes me happy and my potential double up to buys more time for my articles on SQL! J

Post Reference: Vikram Aristocratic Elfin Share

Thursday, December 19, 2013

SQL Server 2008 new Datetime function TODATETIMEOFFSET

Change the time zone offset of the current date and time but unlike Switchoffset, it wont change the time, just it change the offset value ie GMT it changes. Lets take some example to understand this.

TODATETIMEOFFSET ( expression , time_zone )

Lets do some query to understand this

select sysdatetimeoffset() as Current_Time_With_OffsetValue

Current_Time_With_OffsetValue

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

2013-12-19 17:32:48.2740254 +05:30

(1 row(s) affected)

So the current time with offset is 2013-12-19 17:32:48.2740254 +05:30

Let’s apply TODATETIMEOFFSET as well as SWITCHOFFSET to check what we will get

TODATETIMEOFFSET

select todatetimeoffset(sysdatetimeoffset(),'-04:00') as Changed_Time

Changed_Time

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

2013-12-19 17:35:32.3950254 -04:00

SWITCHOFFSET

select switchoffset(sysdatetimeoffset(),'-04:00') as Changed_Time

Changed_Time

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

2013-12-19 08:05:32.3950254 -04:00

Here we can see the with TODATETIMEOFFSET only the offset (GMT) gets changed to -04:00 where as in case of SWITCHOFFSET time and offset (GMT) both gets changed.

Let’s take one few more example

TODATETIMEOFFSET

select todatetimeoffset(sysdatetimeoffset(),'+12:00') as Changed_Time

Changed_Time

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

2013-12-19 17:40:26.3130254 +12:00

SWITCHOFFSET

select switchoffset(sysdatetimeoffset(),'+12:00') as Changed_Time

Changed_Time

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

2013-12-20 00:10:26.3130254 +12:00

Again we can see the with TODATETIMEOFFSET only the offset (GMT) gets changed to +12:00 where as in case of SWITCHOFFSET date, time and offset (GMT) all gets changed.

NOTE: So with TODATETIMEOFFSET you can change the offset only without affecting your date and time where as in case of SWITCHOFFSET you can change date time and offset according to the GMT passed as an argument to SWITCHOFFSET.

Every value code is an artist. But to remain an artist revise required.

Post Reference: Vikram Aristocratic Elfin Share

Tuesday, December 17, 2013

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)

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