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

SQL Server 2008 new Datatime function SWITCHOFFSET

Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. It also change the given time to the offset passed as parameter unlike TODATETIMEOFFSET

 SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

Lets do some query to understand this

select sysdatetimeoffset() as Current_Time_With_OffsetValue

Current_Time_With_OffsetValue

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

2013-12-18 23:16:46.0192745 +05:30

So the current time with offset is 2013-12-18 23:16:46.0192745 +05:30

Let’s apply SWITCHOFFSET to check what we will get

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

Changed_Time

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

2013-12-18 13:53:16.7063393 -04:00

Here we can see the current time and zone changed to 13:53:16.7063393 -04:00

Let’s take one few more example

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

Changed_Time

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

2013-12-18 21:55:38.6735346 +04:00

The current time and zone changed to 21:55:38.6735346 +04:00

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.

Set off jogging with your logic and carry out an elegant and community will call it mastery code.

Post Reference: Vikram Aristocratic Elfin Share

How to store ONLY Time in SQL SERVER?

Only Time in SQL SERVER

Many a time we just need to store time part of date only, but when we look after SQL Server there is no such datatype which allow us to store only time part of the datetime.

So the question of the matter is how to store only time in sql table object. Here is a small exmple of storing time efficiently, is to use an integer column.

create table demoToStoreOnlyTime_tab(

myTotalHour smallint)

Command(s) completed successfully.

Now while inserting, you multiply the number of hours by 100, and add the minutes

insert into demoToStoreOnlyTime_tab  values( 100*datepart(hour,getdate()) + datepart(minute,getdate()))

(1 row(s) affected)

Rest let the developer do there work for formating while displaying time on front end

select CAST(LEFT(myTotalHour, LEN(myTotalHour)-2) + ':'+ RIGHT(myTotalHour, 2) AS VARCHAR(5)) as 'MyHour'  from demoToStoreOnlyTime_tab

MyHour

------

11:16

(1 row(s) affected)

Happy Coding :)

Post Reference: Vikram Aristocratic Elfin Share