Named Queries - Date and Time

Ignition
1

Hi,

I am porting an application from MySQL to Microsoft SQL, and I have a strange occurrence. I have a column which is simply of type “DATE” and a row that contains a value of 2019-10-30. My named query passes a start date (2019-10-30 00:00:00) and a end date (2019-10-30 23:59:59). The SQL Select statement uses "SELECT * FROM table WHERE datecolumn between :startdate and :enddate.

In the testing tab, if I use 2019-10-29 as a start / end date (times stay the same), nothing is returned, but if I pass these through scripting, it returns the 30th data using the 29th as the dates. I played with the times a bit, and this looks like it is a time zone problem. Why would it work in the testing tab, but not through script? Or, why does Microsoft treat it differently than MySQL?

Thanks,

Kory

2

Update - So this is probably not the correct way to fix this, but it works. In my named query, rather than receiving DateTime data types, I send the exact same data, but changed the receiving data type to String.