Link to home
Start Free TrialLog in
Avatar of Varshini S
Varshini S

asked on

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 ?
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

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

Avatar of ste5an
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.
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
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Varshini, do you still need help with this question?