MS SQL’s American origins can sometimes take us poor Brits unawares, and no more so than with date formats.

If, in GoldMine’s SQL editor, you type:


select ondate from conthist order by ondate


Then, in the UK at least, you will get dates returned in the format dd/mm/yyyy (eg ’30/1/2016′). This can lead you to think that SQL will understand them in that format. Not necessarily so! By default, SQL uses the American date format internally, although this can be overwritten at install-time or later. In short, you don’t always know how SQL will interpret something like ‘1/7/2016’.

The solution is simple. In queries, always use the unambiguous date format yyyy-mm-dd. So,


select ondate from conthist
where ondate > '2016-07-01'
order by ondate