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.

SQL SERVER - Datetime Function TODATETIMEOFFSET Example TODATETIMEOFFSET

It is quite clear from the example that only offset changes in the time, but unlike SWITCHOFFSET the datetime remains the same. Datetime datatype stores date and time part. If you have only date value, You can add time, part to a datetime variable using direct literal value and + operator.

In this SQL in Sixty Seconds video we cover two different methods to display the datetime in specific format. 1) CONVERT function and 2) FORMAT function.

Let me know what you think of this video in the comment section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Datetime Function SWITCHOFFSET Example
Next Post
SQLAuthority News – Community TechDays, Ahmedabad – July 24, 2010

Related Posts

3 Comments. Leave new

  • 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!

    Reply
  • Hi Pinal,
    Could you please explain What is function? and its types with simple example.

    Reply
  • 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.

    Reply

Leave a Reply