Colleagues and I forced to use different SQL Server date formats

This is really starting to bug me now, if anyone has ever come across it. We don't have a full fledged DBA here and we can't find a setting that would fix the issue.

Basically, when running queries, each of us has to set the DATEFORMAT differently. My colleague to the left has to SET DATEFORMAT DMY whereas if I pick up any queries she's written I have to actually force the query into SET DATEFORMAT YMD. My other colleague ends up having to do SET DATEFORMAT YDM like the US format.

Is this something that can be changed with a setting somewhere? Of all the workplaces I've used MS SQL Serve I've never come across this before and have no idea how to fix it!

EDIT: In response to comments; yes, these queries will be run on the same server and database.

Solution

You could define date literals as ISO-8601(culture independent):

The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings.

SELECT *
FROM tab
WHERE col = 'yyyy-mm-ddThh:mm:ss';

or:

SELECT *
FROM tab
WHERE col = 'yyyymmdd';