SQL Query does not pick up date range start

Question

• SQL Server 2008 R2
• VB.net
• Silverlight 5

The database is a date type with no time component. In the query I put a time component to ensure proper start and end times. If I adjust the start date to UTZ time the query works. If I adjust the start date to yesterday 1 second prior to midnight it does not work. This indicates to me the database is storing the date as UTZ but that seem impossible.

I have this:

Table

• ID AS int
• dt AS Date

In the table are 2 rows:

ID  dt
------------
1   8/1/2014
2   8/2/2014

Params:

• &FromDate = "8/1/2014 00:00:00"
• &ToDate = "8/2/2014 23:59:59"

Query:

SELECT * 
FROM Table 
WHERE dt >= &FromDate AND dt <= @ToDate

Result:

ID  dt
------------    
2   8/2/2014

The dates are put in the table like this:

MyObject

dt as date

myObject.dt = "8/1/2014"

@dt = myObject.dt

INSERT INTO Table (dt) VALUES (@dt)

Why do I not get both records with this query? How do I fix it?

This works:

FromDate = Format(FromDate, "MM/dd/yyyy 00:00:00.000"):FromDate = FromDate.AddHours(-5)

ToDate = Format(ToDate.AddDays(1), "MM/dd/yyyy 00:00:00.000"):ToDate = ToDate.AddHours(-5)

sb.Append("AND s.AlarmDate>=@FromDate AND s.AlarmDate<@ToDate ")

UPDATE:

A detailed explanation of the environment and the problem.

I have solved this by adjusting the dates as indicated, but I don't like the solution and I don't understand what is causing the problem.

Environment:

Development server is Windows 2003 Server with SQL Server 2008 R2. Server timezone is CDT. Production server is Windows Server 2008 also running SQL Server 208 R2, timezone CDT. Both Development and Production server exhibit the same behavior regarding this problem.

Project is Silverlight 5 website.

Client machine is Windows XP with timezone set to CDT.

To store the record with the date in the database the user initiates a dialog that creates a data object with a date property, this data object is bound to a datepicker.

Lets assume the user select a date of 8/1/2014.

When the user clicks the save button the object is serialized into xml in preperation for posting to a webservice hosted on the server for storage in the database.

The serialization process puts a timestamp in the xml so the xml that arrives at the server has this:

2014-08-01T00:00:00

When the webservice receives this xml it is deserialized into my data object like this:

Dim oNote As New DataNote
Dim ser As New XmlSerializer(oNote.GetType)
Dim myStream As New System.IO.MemoryStream(New System.Text.UnicodeEncoding().GetBytes(xml))
oNote = ser.Deserialize(myStream)
myStream.Close()

After the deserialzation of the xml into my object I re-assign the date with the time stamp removed. I did this to try and fix the problem early on, by using string manipulation and removing the timestamp and assigning only the date portion (as a string) to my object property I am ensuring that if there is a time component it is set to midnight. This step is probably unnecessary.

The data object property that receives the date is of date type, not datetime, as is the sql table field and the parameter of the stored procedure that updates the database.

Inspection of the databse with SQL Server Management Studio from the client or dev machine shows the date as '2014-08-01' in the database.

So far so good.

To query the date and exhibit the problem:

I am using a telerik report, it hosts a objectdatasouce that uses a class I wrote that returns a list of my objects. This code is run server side. The data class uses the SqlConnection provided by ASP.NET, which I assume is ADO to open and query the database.

The query is striaght forward using this clause to locate the date range.

"s.AlarmDate>=@FromDate AND s.AlarmDate<=@ToDate"

If I use a FromDate of #8/1/2014# the query does not find the record. After experimentation I have discovered that I must subtract 5 hours from the FromDate in order to return the record.

This means that the database is acting as though the data in the database is '7/31/2014 19:00:00". This is -5 hours from the 'actual' date which happens to be the UTZ offset for CDT (Central Daylight Time).

It could also mean that my FromDate is 5 hours ahead. This makes even less sense to me so I am dismissing it.

That's it. There are no other layers involved, its as simple as that.

Answer 1

Not an answer but a detailed explanation of the environment and the problem.

I have solved this by adjusting the dates as indicated, but I don't like the solution and I don't understand what is causing the problem.

Environment:

Development server is Windows 2003 Server with SQL Server 2008 R2. Server timezone is CDT. Production server is Windows Server 2008 also running SQL Server 208 R2, timezone CDT. Both Development and Production server exhibit the same behavior regarding this problem.

Project is Silverlight 5 website.

Client machine is Windows XP with timezone set to CDT.

To store the record with the date in the database the user initiates a dialog that creates a data object with a date property, this data object is bound to a datepicker.

Lets assume the user select a date of 8/1/2014.

When the user clicks the save button the object is serialized into xml in preperation for posting to a webservice hosted on the server for storage in the database.

The serialization process puts a timestamp in the xml so the xml that arrives at the server has this:

2014-08-01T00:00:00

When the webservice receives this xml it is deserialized into my data object like this:

Dim oNote As New DataNote
Dim ser As New XmlSerializer(oNote.GetType)
Dim myStream As New System.IO.MemoryStream(New System.Text.UnicodeEncoding().GetBytes(xml))
oNote = ser.Deserialize(myStream)
myStream.Close()

After the deserialzation of the xml into my object I re-assign the date with the time stamp removed. I did this to try and fix the problem early on, by using string manipulation and removing the timestamp and assigning only the date portion (as a string) to my object property I am ensuring that if there is a time component it is set to midnight. This step is probably unnecessary.

The data object property that receives the date is of date type, not datetime, as is the sql table field and the parameter of the stored procedure that updates the database.

Inspection of the databse with SQL Server Management Studio from the client or dev machine shows the date as '2014-08-01' in the database.

So far so good.

To query the date and exhibit the problem:

I am using a telerik report, it hosts a objectdatasouce that uses a class I wrote that returns a list of my objects. This code is run server side. The data class uses the SqlConnection provided by ASP.NET, which I assume is ADO to open and query the database.

The query is striaght forward using this clause to locate the date range.

"s.AlarmDate>=@FromDate AND s.AlarmDate<=@ToDate"

If I use a FromDate of #8/1/2014# the query does not find the record. After experimentation I have discovered that I must subtract 5 hours from the FromDate in order to return the record.

This means that the database is acting as though the data in the database is '7/31/2014 19:00:00". This is -5 hours from the 'actual' date which happens to be the UTZ offset for CDT (Central Daylight Time).

It could also mean that my FromDate is 5 hours ahead. This makes even less sense to me so I am dismissing it.

That's it. There are no other layers involved, its as simple as that.

Answer 2

Try changing your code to this:

&FromDate = "8/1/2014 00:00:00"
&ToDate = "8/3/2014 00:00:00"

and

SELECT * 
FROM Table 
WHERE dt >= &FromDate AND dt < @ToDate

Note the very important changes to the inequality and to the ToDate value. There is an issue in SQL Server with the resolution of times just before and just after the hour, requiring the use of this technique to get correct reproducible results. In practice it is impossible to reliably set a datetime value to just before a date change.

Also, it is very highly recommended that you either use the format 'yyyymmdd' for date strings being implicitly converted to dates (or dtetimes), or explicitly select a specific format using the CONVERT function

Update - after comments from OP above.

Always use the most specific possible datatype for parameters to stored procedures. Change the datatype of the date parameter in your stored procedure to DATE, so that you are in control of the conversion to/from character.

Answer 3

My guess is that it's the time component of the row w/ the 8/2 date.

For instance if it were:

insert into tbl values (2, '08/02/2014 23:59:59.999');

That row would not come back in your query (notice the .999) Fiddle: http://sqlfiddle.com/#!3/6dc42/1/0

But if the row were:

insert into tbl values (2, '08/02/2014 23:59:59');

That row would come back (no .999) - Fiddle: http://sqlfiddle.com/#!3/ed49b/1/0

Try changing the parameter to the following value (with nnn seconds):

select *
from tbl 
where dt >= '8/1/2014 00:00:00' and dt <= '8/2/2014 23:59:59.999'

Fiddle: http://sqlfiddle.com/#!3/6dc42/3/0

See http://msdn.microsoft.com/en-us/library/ms186724.aspx

Alternatively if you can change the condition to 'less than' rather than <= you could could do < the third at 0 hours.