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)
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!
[...] SQL SERVER – Datetime Function TODATETIMEOFFSET Example [...]
Hi Pinal,
Could you please explain What is function? and its types with simple example.
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.
[...] and SYSDATETIME Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime Datetime Function TODATETIMEOFFSET Example Information Related to DATETIME and DATETIME2 Datetime Function SWITCHOFFSET Example Difference [...]