Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email


SQL date format

05/08/2016 – in GoldMine, SQL queries

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