SQL SERVER – Datetime Function TODATETIMEOFFSET Example

July 16, 2010

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

SQL SERVER – Datetime Function SWITCHOFFSET Example

SQLAuthority News – Community TechDays, Ahmedabad – July 24, 2010

3 Comments. Leave new

ny
July 20, 2010 7:11 pm
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
vanthiya
September 6, 2010 5:23 pm
Hi Pinal,
Could you please explain What is function? and its types with simple example.
Reply
shalini
October 19, 2010 11:36 am
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