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 ?
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 ?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Actually DB collation is not affecting date format. But still you can try this
...where CONVERT(varchar,RecordDate,109)='Dec 31 2015'
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.
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
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
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
>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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial