Query between two dates

Permanently deleted user

June 25, 2018 21:50

I am trying to run a very simple query between two dates.

 

INSPDATE1 is the date field in the data.

 

outStartDateString and ourEndDateString are the date variables converted to strings.

 

The following query returns no results:

 

"INSPDATE1 >= '" + outStartDateString + "' AND INSPDATE1 <= '" + outEndDateString + "'"

 

When I run only the first portion in the query task:

 

"INSPDATE1 >= '" + outStartDateString + "'"

 

it works fine.

 

Ideas?

0

• 

  Tom Neer

  June 26, 2018 15:38

  What type of database (Oracle, SQL Server, PostgreSQL, SDE, other) are you querying against? 

  0
• 

  Permanently deleted user

  June 26, 2018 15:52

  Hi Tom...we are querying against a SQL database. And we've discovered that this query semi works:

   

  "INSPDATE1 >= DATE '" + outStartDate.ToString() + "' AND INSPDATE1 <= DATE '" + outEndDate.ToString() + "'"

   

  ...but will not return the selected Start Date. For example, if I select August 1st as the start date and August 15th as the end date, I only get results for August 2nd - August 15th. However, if I select July 31st as the start date, and August 15th as the end date, I then get the August 1st results....very weird...

  0
• 

  Tom Neer

  June 26, 2018 16:11

  Your query is valid but Assuming SQL Server native, try: SELECT ... FROM ... WHERE inspdate1 BETWEEN '" + outstartdate.ToString() + "' and '" + outenddate.ToString() + "'; You may need to change "inspdate1" to "CAST(inspdate1 AS DATETIME)". Curious to the results of that query first.

   

  If that works but you still have a the STARTDAY + 1 issue, you should look how the data is stored in your database (i.e. what timezone) and also what timezones your database server and web server are set to. My first guess is one is set to local and another to UTC. 

   

   

  0
• 

  Permanently deleted user

  June 26, 2018 21:13

  Hi Tom! Thank you for the suggestion, unfortunately using a between statement still isnt returning the start date.

   

  "INSPDATE1 BETWEEN DATE '" + outStartDate.ToString() + "' AND DATE '" + outEndDate.ToString() + "'"

   

  Also, as far as I can tell, all of our timezones are set corretly in our databases and servers.

  0
• 

  Tom Neer

  June 26, 2018 21:43

  Lisa - I would start looking at the data. If possible, use a SQL console or Query Layer to query the database with some sample data. We ran into an similar issue because the data was stored with no time value but the web interface was sending the UTC offset so "2017-01-01 00:00:00" was less than "2017-01-01 07:00:00" and returned no results. We had to build in the offset then the query executed correctly. 

  0

Please sign in to leave a comment.