CODEWITHSUNDEEP
sqlsql-server
Jack Kada
Jack Kada

Reputation: 25192

Formatting Dates In SqlServer To Use As Comparison

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]

Upvotes: 0

Views: 148

Answers (4)

KMB
KMB

Reputation: 162

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)

  • 521002 2008-09-1500:00:00.000 2008-09-15
  • 580195 2008-04-1500:00:00.000 2008-04-15
  • 530058 2008-09-2200:00:00.000 2008-09-22
  • 580194 2008-04-0200:00:00.000 2008-04-02
  • 500897 2008-07-0100:00:00.000 2008-07-01
  • 500966 2008-09-2300:00:00.000 2008-09-23

Upvotes: 0

erikkallen
erikkallen

Reputation: 34391

Your last try was almost correct, but it should have been

SELECT * FROM TABLEA WHERE AS_OF_DATE = CONVERT(DATETIME, '01/01/2010', 101)

Upvotes: 1

Frank Kalis
Frank Kalis

Reputation: 1352

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.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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)

Upvotes: 1

Related Questions