Understand date format required in a sql statement

I always forget this, and stumble across from time to time: the format of a date value that I want to include directly in a x++ sql statement where clause.

What you want is something like this:

projTable projTable;

select * from projTable
    where projTable.Created > _actual_date_value;

The applications for this are existing in real life, so, how to pass the value?
Let's say I want to use as the filter 22-Feb-2016. I always try at first with  1/22/2016, 1-22-2016 or even '1/22/2016'. Of course they fail.

Then I remember the proper format which is 22\1\2016. Notice here that the format is dd\MM\yyyy. The day comes before the month, even if my region is set to English.

However, thanks to another discussion I was made aware of another possible approach: using the str2date function in ax. So if you were to use str2date('22-1-2016', 123) it will work beautifully. In this case you can even go with whatever separator you want, like / or - or \ and choose your date sequence.

No comments:

Post a Comment