Query date instead of datetime docdb

Question

I am using docdb. I would like to query the processTime with date only instead of datetime. My input pdate = 26-09-2017 which is dateonly. I am using cast function to convert datetime to date in sql query but I am getting syntax error. I am doing this in c#. Using LINQ for query

I have the following json in document db.

[{
 "processTime": "2017-09-26T21:05:28.7954106+05:30",
 "submittedBy": "671"
}
{
 "processTime": "2017-09-26T21:05:28.7954106+05:30",
 "submittedBy": "679"
}
{
 "processTime": "2017-09-26T21:05:28.7954106+05:30",
 "submittedBy": "679"
}
]

I am querying the db as follows

            new SqlQuerySpec()
            {
                QueryText = "SELECT * FROM cols e WHERE e.submittedBy = @ci AND CAST( e.processTime AS int) = @date",
                Parameters = new SqlParameterCollection()
                {
                new SqlParameter("@ci",  cid.ToString()),
                new SqlParameter("@date", pdate)
                }
            }, DefaultOptions);

I am getting syntax error near CAST function

{"Message: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":61,\"end\":65},\"code\":\"SC1001\",\"message\":\"Syntax error, incorrect syntax near 'CAST'.\"}]}\r\nActivityId: 5661ff3b-64cb-46d7-8c9e-0125145a8fb3"}

Larry Maccherone · Accepted Answer · 2017-09-27 13:56:10Z

@nacho's answer looks like it will work. However, the beauty of the ISO-8601 format is that you can also just do string comparisons. So the SQL query becomes:

SELECT * FROM cols e WHERE e.submittedBy = @ci AND STARTSWITH( e.processTime, @dateString)

@dateString should be a string in the format like "2017-10-20".

Note, you can also do date ranges this way and as long as there is a range index on the submittedBy field by using the inequality operators > and <=. Note however, that the second date in the range is exclusive, while the first one is inclusive.

I also notice that you use ISO-8601 strings with timeshift format. That's fine as long as you can be sure the data for any single query always has the same shift. However, if you want more control over it, store it in zulu time (GMT) and do the shifting at query time by adjusting the string literals at query time. I've written up this approach in a gist here and the tzTime and Lumenize libraries mentioned in that writeup were built to facilitate that approach.

nacho · Accepted Answer · 2017-09-27 08:26:06Z

1

Try using the DATE() function to get only the day of the processTime column like this:

        new SqlQuerySpec()
        {
            QueryText = "SELECT * FROM cols e WHERE e.submittedBy = @ci AND DATE( e.processTime) = STR_TO_DATE(@date,'%d-%m-%Y')",
            Parameters = new SqlParameterCollection()
            {
            new SqlParameter("@ci",  cid.ToString()),
            new SqlParameter("@date", pdate)
            }
        }, DefaultOptions);

You need to convert your pdate value from dd-mm-aaaa to aaaa-mm-dd with

STR_TO_DATE(pdate,'%Y-%m-%d')

answered Sep 27, 2017 at 8:26

nacho

5,3772 gold badges28 silver badges35 bronze badges

Collectives™ on Stack Overflow

Query date instead of datetime docdb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related