Please specify DateTime conversion fully

[ishepherd]

I suspect that the second fraction always has 7 digits and is always followed by a Z. Please confirm?

We have had an issue recently from inexact string/date comparison in cosmos queries, so I'd like this to be firmly specified

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: c256dbf4-122d-1569-c4c3-0e401766c712
• Version Independent ID: 747c3f05-167d-2048-6f1b-fa1d7d856f66
• Content: Working with dates in Azure Cosmos DB
• Content Source: articles/cosmos-db/working-with-dates.md
• Service: cosmos-db
• GitHub Login: @SnehaGunda
• Microsoft Alias: sngun

[Mike-Ubezzi-MSFT]

@ishepherd Thank you for the detailed feedback. I have assigned this to the content author to evaluate and update as appropriate.

[ishepherd]

Simpler version of the question:

Are you using the .NET DateTime "o" format to make the ISO 8601 conversion?

I would check myself but the library is closed source.

[SnehaGunda]

@ishepherd Sorry abou the delay in getting back on this, we use the ISO data format: https://en.wikipedia.org/wiki/ISO_8601 you have to serialize the datetimeoffset as a lexographically sorted string in order for Cosmos DB to complete the operation.

We will now close this issue, if you have further questions feel free to reopen.

[SnehaGunda]

#please-close

[ishepherd]

Hi @SnehaGunda, please don't close. Here is an example:

Say I have a document with

Time =  "2018-01-01T00:00:00.0001Z"

and I run a query WHERE

Time <= "2018-01-01T00:00:00.00010Z"

This query will not match the document. The strings are equal times, but they are not equal lexicographically, due to the different decimal places. ISO 8601 does not specify decimal places.

Therefore please answer,

Are you using the .NET DateTime "o" format to make the ISO 8601 conversion? [7 decimal places]

[ishepherd]

@MarileeTurscak-MSFT Please reopen. @SnehaGunda said I could reopen if question not answered, but I guess I do not have rights to do it.

Sneha, please see my comment above #19284 (comment)

[Mike-Ubezzi-MSFT]

@ishepherd Understand that you are seeking a product related question. This channel is for addressing documentation related issues. Please take a look at the the Cosmos DB MSDN threads related to date time conversion. If after researching this you are at a complete loss, please use the email alias: askcosmosdb@microsoft.com. Thank you.

[daxfohl]

@ishepherd It uses Newtonsoft under the hood, so thus it uses whatever global settings Newtonsoft is configured with. You can override these settings in the DocumenClient constructor, so if you create your DocumentClient with serializerSettings: new JsonSerializerSettings { DateFormatString = "o" }, it'll fix the problem. Ick, but so it goes.
@Mike-Ubezzi-MSFT and @SnehaGunda this seems like something worth documenting, as it is far from obvious that this would be necessary, and it breaks DateTime sort order without it. Given this is the canonical document on how to use DateTime in Cosmos DB, this guidance should be here.

[ishepherd]

👍 @daxfohl but note that serializerSettings is not a full solution because of Azure/azure-cosmos-dotnet-v2#351.

[daxfohl]

Yeah, in fact it doesn't appear to work at all. Still looking for a solution to this myself.

…

On Thu, Jan 3, 2019 at 5:02 PM Iain Shepherd ***@***.***> wrote: 👍 @daxfohl <https://github.com/daxfohl> but note that serializerSettings is not a full solution because of Azure/azure-cosmos-dotnet-v2#351 <Azure/azure-cosmos-dotnet-v2#351>. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#19284 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3PcYQ_Fvl0b3enexqPqY2ZCngfDqd8ks5u_qhBgaJpZM4YsYtx> .