Dates are tricky in any environment. Simple answer, always use YYYY-MM-DD format for dates.

A lot of software written in the US by many companies including Accruent simply says 'the way the USA does it is the correct and only way'

All our dates/datetimes are in UTC (Universal Coordinated Time, aka Greenwich, aka Zulu)

All dates are coming from the API are designed to be

All date and datetimes passed to us are to be in ISO UTC date format:

YYYY-MM-DDThh:mm:ss.sssZ

2023-04-20T18:28:48.962Z

The Z means "Zulu" for historical reasons. Z means you either put the literal Z or you put -<timeoffset> such as -6:00

When passing a date in a where clause, you should always do comparisons

When doing where clause statements, you should NOT put in literal strings for dates, if something changes on the server, your code may stop working.

targetDate < DateTime(2007, 6, 22)

Now, you may be asking why not just put in a string and let SQL Server compare it. The problem is, unless you use YYYY-MM-DD format, the conversion that SQL makes will depend on several factors. The problems are worst for the 1st 12 days of every month because SQL can't tell which is the day and which is the month, so it uses a set of rules to decide – and those rules can change depending on changes to the server, versions of SQL (although to be fair – they try to not make too many changes of this type), location of the server (If it moves to a different timezone you could end up having certain conversions suddenly be 1 day off), or the SQL server or System server is changed from mm/dd/yyyy to dd/mm/yyyy or the other way around. Further, the date formats in MRO make differences based on country code, where only UK, Canada, Australia and USA work. Others like Germany and Italy have worked wrong since 2003, so you might have it working today, and then if Accruent ever fixes those bugs, suddenly it will stop working. While this last doesn't directly affect our API, if the data is being stored from the Accruent UI differently, the API calls might work based on how Accruent used to store the data.

The summary is, if you find that entering dates in dd/mm/yyyy format or mm/dd/yyyy format works right now … please assume that when things change or when servers move, or when you try it on another server, it may appear to work (no error messages) but it will be at risk of not working the way you want. And if you still do not want to listen to this advice, at least make sure you test during the first 12 days of the month, but NOT on the day that matches the month number. So, testing on January 1st, February 2nd …December 12th or any month on day 13 or higher should be considered invalid testing. And now that you read this, hopefully you'll say "OK fine, I'll use ISO YYYY-MM-DD dates and UTC times."

In any event, the goal is to minimize problems, and by telling SQL Server DateTime(YYYY,MM,DD) you are telling it EXACTLY how to convert the data into the correct date, and then you don't have to worry about all those other issues, at least for your calls.

All data returned will be in ISO UTC date format.