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.

TODATETIMEOFFSET SQL SERVER   Datetime Function TODATETIMEOFFSET Example

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)

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!

    Like

  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.

    Like

  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