Examples of DATETIMEOFFSET
1. Store a Timestamp with Time Zone Offset
DECLARE @OrderTime DATETIMEOFFSET = '2024-05-10 13:45:00.0000000 -08:00';
Explanation: The above query will store an order time in Pacific Standard Time (PST) 8 hours behind UTC.
Output:
2. Convert DATETIMEOFFSET Data Type to Other Date and Time Types.
DECLARE @datetimeoffset datetimeoffset(4) = '12-12-2 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;
SELECT @datetimeoffset AS '@Gfgtimeoffset ', @date AS 'date';
Explanation: The above query creates a datetimeoffset variable then extracts the date part from it and then assign the date to a separate variable and then it displays both the values.
Output:
3. Creating a Table with DATETIMEOFFSET
CREATE TABLE EventSchedule (
EventID INT PRIMARY KEY,
EventName NVARCHAR(100),
EventDateTime DATETIMEOFFSET
);
INSERT INTO EventSchedule VALUES
(1, 'Conference', '2023-01-15T08:00:00-05:00'),
(2, 'Webinar', '2023-02-20T15:30:00+02:00');
Explanation: In the following example we have created a table called EventSchedule with three columns including EventDateTime which stores the data in DATETIMEOFFSET format. Then we have added two events with their dates, times and time zone offsets.
Output:
4. Querying with DATETIMEOFFSET
-- Retrieve events that occur after a specific date and time
SELECT *
FROM EventSchedule
WHERE EventDateTime > '2023-02-01T00:00:00+00:00';
Explanation: In the above example we have executed a query that fetches events happening after the second month of 2023 regardless of their time zones.
Output:
5. Convert DATETIMEOFFSET Value to a Different Time Zone
First create the following table with a DATETIMEOFFSET column.
CREATE TABLE events(
unique_id INT PRIMARY KEY IDENTITY,
description VARCHAR(255) NOT NULL,
event_time DATETIMEOFFSET NOT NULL
);
Insert dummy data into the table:
INSERT INTO events(description,event_time)
VALUES('w3wiki meet ',
CAST('2023-12-12 08:45:00.0000000 -08:00' AS DATETIMEOFFSET));
Now execute the following query to convert the standard Pacific timezone to Asisan Standard Timezone.
SELECT
unique_id,
description,
event_time AS 'Pacific Standard Time',
event_time AT TIME ZONE 'SE Asia Standard Time' AS 'SE Asia Standard Time'
FROM
events;
Output:
SQL Server DATETIMEOFFSET Data Type
When we are working with different times and dates in the SQL server we struggle with the different time zones. In this article, we are going to explore the data type DATETIMEOFFSET in SQL Server which helps us to resolve this problem. But before you deep dive into this article make sure you are familiar with the basics of SQL Server and data types like DATETIME and DATETIME2.