What is the best way to handle SQL Server date collation ?

Varshini S
Varshini S used Ask the Experts™
I am using the below SQL date format ('Dec 31 2015')  in my SQL SP across the board.

Select name,age,salary from employee where RecordDate='Dec 31 2015'

Will it be an issue if the SQL collation change ?
 In that case, what is the SQL date format (to support any collation)  i can use in my SP ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Actually DB collation is not affecting date format. But still you can try this
...where CONVERT(varchar,RecordDate,109)='Dec 31 2015'

Open in new window

ste5anSenior Developer

It's better to use a nonambiguous format for your literals. Yours depends on language and order (SET DATEFORMAT).

The most common format is YYYY-MM-DD. It's also the ISO 8601 standard, which is the only international date format.

The second neutral format is the ODBC format: { d 'yyyy-mm-dd' }  

See also Supported String Literal Formats for date.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

there are 2 "safe" date literals in SQL Server


           nb: YYYY-MM-DD by itself is NOT 100% safe also see: Bad habits to kick : mis-handling date / range queries

NEVER use a language dependent literal that depends on the name of a month

Select name, age, salary from employee where RecordDate = '20151231' --YYYYMMDD is safe
Software Developer
>what is the SQL date format (to support any collation)  i can use in my SP ?

Like Paul Maxwell said. More general speaking the  ISO 8601 format is supported since SQL Server 2000 and does not depend on session or language settings.

In regard of dates ISO 8601 means 'YYYYMMDD', in regard of datetimes  ISO 8601 means 'YYYY-MM-DDThh:mm:ss.nnnZ'
where you replace each letter with digits, of course, except T and Z. T is just T, the separator of date and time part and Z is Z, stands for Zulu time or Zero offset from UTC, other time zones are specified with + or - hh:mm, the mm part does not need to be 00, there are time zones shift N+half an hour (eg look at news about north korean time zone change)

You can leave out Z or a time zone shift and this would widely be used and interpreted as local time. If you don't need time zones the other dateformat and language setting independent format is "Unseparated": YYYYMMDD hh:mm:ss', also see http://www.karaszi.com/sqlserver/info_datetime.asp

Bye, Olaf.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Varshini, do you still need help with this question?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial