This should be easy
I have a date column on table A
SELECT * FROM TABLEA WHERE DTE = '01/02/2010'
The problem is that this is deployed onto US servers and I have no control over the date as its an arg.
How can I tell SqlServer to treat this date as being in that format??
I gave tried this:
SELECT * FROM TABLEA WHERE DTE = CONVERT(VARCHAR(10), '01/01/2010' , 101) AS [DD/MM/YYYY]
Use a safe format. For dates (without a time component), the safe format is YYYYMMDD, e.g. today is '20100209'.
For datetimes, the safe format is YYYY-MM-DD'T'HH:mm:SS, where 'T' is the literal T, so right now is '2010-02-09T11:10:30'.
(When I'm saying safe, I mean that SQL Server always, unambiguously, knows how to convert these strings into datetime values)
Check out this reference article: The ultimate guide to the datetime datatypes
EDIT: Specifically what Tibor says about SET DATEFORMAT & SET LANGUAGE, since you mention that you have no control over the input format.
Your last try was almost correct, but it should have been
SELECT * FROM TABLEA WHERE AS_OF_DATE = CONVERT(DATETIME, '01/01/2010', 101)
Another option is a double conversion (check performance when used as criteria):
select strTempNo, dtmTempDateStart,
convert(varchar(10), Convert(datetime, dtmTempDateStart, 103), 126) As UTCDate
I use 103 here as the data is already in UTC format but this works as well (UTC ISO8601 is 126). If your dates are known to be always in American format you have to use 101.
Alternatively use 112 (ISO "safe" format) and cut the first 8 characters out of the string.
Data sample: (Sorry, don't have an American date table available)