Convert string to datetime in cosmos db

user_5876666 0
Viewed 4874

In my cosmos db a date field is stored as string like this { "ValidationWeekStartDay": "27-Apr-2020" }

I have to write a query to extract all documents whose ValidationWeekStartDay is greater than current date. How can I achieve this in cosmos db query?

Select * from c wher c.ValidationWeekStartDay > GetCurrentDateTime ()

this does not give me correct result.

azure-cosmosdb date-conversion datetime sql
1 Answers

This is the problem with date format, the documents you are storing is in format 'dd-MMM-yyyy' while GetCurrentDateTime() function gets the date in format 'yyyy-mm-dd....'. So when you run the above query, comparison like below happens:

'27-Apr-2020' > '2020-08-17'

It compares the characters one by one and first 2 characters of first value becomes greater than second value. For testing purpose, anything above date 20 will be returned by your query irrespective of any month.

There are 2 ways to resolve this.

  1. Store the date in same format as GetCurrentDateTime() function.

  2. Create a udf like below. You can create your own udf, this is just a sample one based on date format.(pardon the formatting, you can copy and run it as it is)

    function formatdatetime(datetime){ datetime = datetime.substring(7,11) + '-' + datetime.substring(3,6) + '-' + datetime.substring(0,2); datetime = datetime.replace('Jan','01'); datetime = datetime.replace('Feb','02'); datetime = datetime.replace('Mar','03'); datetime = datetime.replace('Apr','04'); datetime = datetime.replace('May','05'); datetime = datetime.replace('Jun','06'); datetime = datetime.replace('Jul','07'); datetime = datetime.replace('Aug','08'); datetime = datetime.replace('Sep','09'); datetime = datetime.replace('Oct','10'); datetime = datetime.replace('Nov','11'); datetime = datetime.replace('Dec','12'); return datetime; }

And then use the below query:

select c.stdDates as stdDates from c Where udf.formatdatetime(c.stdDates) > GetCurrentDateTime ()

user_137061310
Related