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

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 ?
Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
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

0
ste5anSenior DeveloperCommented:
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.
0
PortletPaulfreelancerCommented:
there are 2 "safe" date literals in SQL Server

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

           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
0
Olaf DoschkeSoftware DeveloperCommented:
>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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Varshini, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.