sql - Arithmetic overflow error converting datetime's -

sql - Arithmetic overflow error converting datetime's -

i have table stores date info in nvarchar in format of (dd/mm/yyyy),when convert column datetime using convert(nvarchar(100), dt, 101) doesn’t have issue ,however when want select top x rows ,i got next error:

msg 8115, level 16, state 2,arithmetic overflow error converting look info type datetime.

the next sample of code:

declare @d nvarchar(100); set @d='20/11/2012' select top 1 @d, (select date_diff = datediff( day, cast(convert(nvarchar(100), @d, 101) datetime), '2014-10-01 00:00:00')) d

it's sql interpreting first date time 11th day of 20th month in year 2012.

try in 1 of sql's preferred date formats.

my preferred format dd-mmm-yyyy because never ambiguous:

declare @d nvarchar(100); set @d='20-nov-2012' select top 1 @d, (select date_diff = datediff( day, cast(convert(nvarchar(100), @d, 101) datetime), '01-oct-2014 00:00:00')) d

sql picks date format based on language.

you can explicit , tell utilize dmy format in query so:

set dateformat 'dmy'

based on comments below inferring sort of query:

--pretending table in database varchar dates :( create table #test (dt varchar(100)) insert #test values ('20/11/2012') set dateformat 'dmy' --works --set dateformat 'mdy' --doesn't work select top 1 dt, (select date_diff = datediff( day, cast(convert(nvarchar(100), dt, 101) datetime), '2014-10-01 00:00:00')) d #test drop table #test

but in case selecting varchar date table, setting date format correct's issue. seek testing differing dateformats.

sql sql-server