SQL Server Datetime Conversion In Select Statement

Question

I have a database table that has a date_time column. I created a view where I pull records from the table where the date_time value is in between two dates. For the sake of example, let's say the view is pulling all records between '2015-04-20' AND '2015-04-26'. When I check the min and max dates, however, the min date is 2015-04-19 17:01:49.000

CREATE VIEW [dbo].[testview]
AS
SELECT * FROM table WHERE
EventDateTime BETWEEN '2015-04-20' AND '2015-04-26'
ORDER BY EventDateTime ASC

I have records from January 1, 2014 to April 29, 2015 in this table. When I check the results, they look ok for the most part, except for some reason it keeps pulling a record with a 2015-04-19 17:01:49.000 timestamp. All other recoirds are within the filter. WHY?

The data points being inserted into the database are in Pacific time instead of UTC. No changes are being made, the data is just being generated with a PDT timestamp, and that data is being directly inserted into the database without manipulation.

SQL Server has no concept of a time zone and inherits the system time from Windows. The timezone of the server that this install of SQL Server is on is Central time. Is SQL Server internally converting the times based on it's own system time, i.e. since it's in Central time, it assumes that all data_points are in UTC and then converts to Central time? Instead of searching for date_times above 2015-04-20 00:00:00 is it searching for records where the date_time is above 2015-04-19 18:00:00? Yet if that were the case, the above timestamp still wouldn't work.

Can someone please explain what is happening?

SQL Server does have concept of time zones if `datetimeoffset` is used. I don't suppose that is the data type you are using? — Mark Sinkinson, Apr 29 '15 at 22:14
@Mark: Nope, the data type for my date_time column is datetime. — user3150002, Apr 29 '15 at 22:40
Which version of SQL Server are you using? [`datetimeoffset`](https://msdn.microsoft.com/en-us/library/bb630289(v=sql.100).aspx) has been available since SS2008 — Tony, Apr 29 '15 at 23:14
`datetime` type doesn't know/care about the timezone, so your query should not return value `2015-04-19 17:01:49.000`. Check in SSMS the actual query and output. Maybe you are looking at the output in your application and that application manipulates the results somehow. Maybe you are just looking at a wrong column. — Vladimir Baranov, Apr 30 '15 at 00:25
Please post some sample data and the query you are using, as written there's nothing I can see as to the reason you are getting dates outside the range. `BETWEEN` is inclusive of the range specified but that does not account for what you are seeing. — Tony, May 01 '15 at 22:29

score 0 · Answer 1 · edited May 23 '17 at 12:02

Since you are using SQL Server 2005 and datetimeoffset is not available as a data type you can add a field to your table in which you can store the time zone information. See this answer to another SO question and a similar question answered by Jon Skeet.

You can then use that information in your queries to adjust for the time zone offset.

Another method is to store the offset in minutes using smallint as discussed here.

You can either add a computed column to the table:

CREATE TABLE Table1 (
    DateTimeUTC datetime,
    OffsetMinutes smallint,
    DateTimeLocal AS DATEADD(minute, OffsetMinutes, DateTimeUTC)
)

or perform the calculation dynamically in a query:

SELECT DateTimeUTC
     ,OffsetMinues
     ,DATEADD(minute, OffsetMinutes, DateTimeUTC) as DateTimeLocal
FROM Table1

There are also other articles/discussions I found relating to the handling of timezones, good luck!

The ultimate guide to the datetime datatypes

How to Handle TimeZone Properly in SQL SERVER?

Thank you for this; I think I will implement this. However, it doesn't really answer my question: why am I getting a record with a timestamp of 2015-04-19 17:01:49.000 if I am pulling all records between 2015-04-20 and 2015-04-26? I am trying to understand the underlying cause. — user3150002, Apr 30 '15 at 00:03

SQL Server Datetime Conversion In Select Statement

1 Answers1