Convert TimeScript to Date in Azure CosmosDB SQL Query

Question

How can I select the timescripts of my documents in Date readable format? I want to do something like this:

SELECT CAST(C._ts AS DATE) FROM C

Specific to Cosmos DB SQL Query only please.

Answer 1

Functions announced in October 2020 https://devblogs.microsoft.com/cosmosdb/new-date-and-time-system-functions/ can assist with this (taking care to convert seconds to milliseconds)

SELECT TimestampToDateTime(C._ts*1000) as DateTime FROM C