0

I have a login user1 with Default Language as 'British English'. when this is changed to 'English' and run the below query

select 
CAST([S1OnsetDATETIME] AS DATETIME)
from
tablename1

It doesn't fail, but when the Language is changed to 'British English'it fails with the error message :

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I have to use the 'British English' and how to sort this out?

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Dev
  • 11
  • 3
  • Use `convert()` with the appropriate format for your date/time value. – Gordon Linoff May 20 '20 at 16:09
  • 2
    Better idea - don't store dates as strings. – SMor May 20 '20 at 16:13
  • What format is your datetime currently as a string and what format are you looking for? Datetime for British English is dd/mm/yyyy while English is mm/dd/yyyy I believe based on this: https://www.w3schools.com/sql/func_sqlserver_convert.asp. https://stackoverflow.com/questions/1187127/sql-server-datetime-issues-american-vs-british But you're going to need to convert() like Gordon has suggested. – Dresden May 20 '20 at 16:19
  • Does this answer your question? [SQL Server Datetime issues. American vs. British?](https://stackoverflow.com/questions/1187127/sql-server-datetime-issues-american-vs-british) – Amira Bedhiafi May 20 '20 at 16:20
  • Tried OnsetAwarenessOfSymptomsDate = CONVERT(DATE,dbo.udfDateOnly(dbo.udfEmptyStringToNull([S1OnsetDATETIME])),120) still failed – Dev May 20 '20 at 17:07

0 Answers0