When parsing Date it has to be parsed into either DateTimeKind.Utc, DateTimeKind.Local or DateTimeKind.Unspecified. By default ServiceStack.Text assumes UTC on the wire and converts it into LocalTime but when using SkipDateTimeConversion it will skip the conversion and assume the Short date is in local time so
"\"2018-10-23\"".FromJson<DateTime>() == new DateTime(2018,10,23) //DateTimeKind.Local
"\"2015-01-23\"".FromJson<DateTime>() == new DateTime(2015,01,23) //DateTimeKind.Local
The TimeZone when serializing into a DateFormat that includes the TimeZone is the local timezone of the computer serializing the date. So if you have 2 different serialized TimeZone’s they’re potentially being serialized with computers in different TimeZones or perhaps it’s a result of Daylight savings. But the TimeZone isn’t contained in the DateTime, if it serializes the TimeZone its using the local TimeZone.
I’d only recommend serializing DateTime in UTC on the wire so it’s always serialized with the same time and :0000 timezone irrespective what local TimeZone the pc is in, by using SkipDateTimeConversion you’re skipping converting Date’s to UTC.
These issues aren’t related, the difference between MyDateBetween is that it uses BETWEEN / AND SQL syntax which is not recommended for Dates as the behavior varies between different RDBMS’s and SQL Server has some gotchas.
The recommendation for RDBMS is to always use WHERE DateField >= @p1 AND DateField < @p2 syntax which in AutoQuery you could use:
Using YYYY-MM-DD solved my issue with Between. FWIW I was using AutoQuery Data, not RDBMS. Is Between safe to use with In Memory Datasets?
Regarding the timezone offset issue, the results were from my developer workstation, so it’s just one box in a single call. Changing SkipDateTimeConversion to false doesn’t change the result. The only other thing unique to my config would be that I am converting the overall object type( based on Show POCO names instead of DataMember names). Any other ideas?