SQL SERVER – Datetime Function TODATETIMEOFFSET Example

Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of TODATETIMEOFFSET as well.

Let us go over the definition of the TODATETIMEOFFSET  from BOL: Returns a datetimeoffset value that is translated from a datetime2 expression.

What essentially it does is that changes the current offset only  offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Let us see the resultset below.

It is quite clear from the example that only offset changes in the time but unlike SWITCHOFFSET the datetime remains the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Datetime Function TODATETIMEOFFSET Example

  1. I’m trying to write a query that allows me to calculated the number of days elapsed between admissions.

    Table Example:

    PatientAcct|AdmissionDate|DischargeDate|Facility
    1234567|1/1/2010|1/5/2010|WMC
    1234567|1/6/2010|1/10/2010|WMC
    2345678|2/1/2010|2/2/2010|WMC
    3456789|2/3/2010|2/5/2010|WMC
    3456789|2/7/2010|2/10/2010|WMC
    3456789|2/11/2010|3/1/2010|WMC

    Current Query: (it calcuates the difference between the dates without checking if the disharge date occurred before the admission date)~(not what I want)
    > > SELECT PatientAcct, AdmissionDate, DischargeDate, Facility,
    > > DATEDIFF(day, DischargeDate,
    > > (SELECT MAX(AdmissionDate) AS AdmissionDate
    > > FROM dbo.Test_Readmission AS B
    > > WHERE (A.PatientAcct = PatientAcct) AND
    > > (A.AdmissionDate > DischargeDate))) AS ElapsedDay
    > > FROM dbo.Test_Readmission AS A
    > >
    > > Expected results:
    > > PatientAcct|AdmissionDate|DischargeDate|Facility|E lapsedDays
    > > 1234567|1/1/2010|1/5/2010|WMC|
    > > 1234567|1/6/2010|1/10/2010|WMC|1
    > > 2345678|2/1/2010|2/2/2010|WMC|
    > > 3456789|2/3/2010|2/5/2010|WMC|
    > > 3456789|2/7/2010|2/10/2010|WMC|2
    > > 3456789|2/11/2010|3/1/2010|WMC|1
    > >
    > > Anyone know what the code is to get it to calculate the date from last
    > > discharge date to current admission date? I’m creating a SQL view in SQL
    > > 2005. Thanks for help!

  2. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  3. SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
    SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘-04:00′) ‘GetCurrentOffSet-4′;
    SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘-02:00′) ‘GetCurrentOffSet-2′;
    SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘+00:00′) ‘GetCurrentOffSet+0′;
    SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘+02:00′) ‘GetCurrentOffSet+2′;
    SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘+04:00′) ‘GetCurrentOffSet+4′;

    These functions telling the below error while i’m executing these in sql server 2008

    ‘SYSDATETIMEOFFSET’ is not a recognized built-in function name.

  4. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s