Date format issue SQL Server

Question

I have a source third party system that uses a Firebird database. You can setup a Windows service of this third party system to write data into a sql server database. Recently I get these exceptions:

00:00:40 CallProcessor.exe##Error##22##[The conversion of a varchar data type to a datetime data type resulted in an out-of-range value@006D5C65] @ :TMSSQLAccess.ExecuteQuery - exception running query : ##Threadid:12576##

The reason must be that my database language is not correct - I think. This is my current setting:

select @@language
British

Looking at Firebird's text data, I can see entries like this (I have written a C# client to parse firebird's raw data):

31/08/2011 16:58:00

This looks correct to me (British format). However, if I do:

select getdate()

I get:

2016-02-18 08:18:27.477

So I am a bit confused about what to do. Please note that I cannot touch the server (i.e. change its language/time zone/date format). However, I have full control of the SQL Server.

PS:

I just traced an insert statement it looks like this:

insert into calls (  site, calldateandtime, initial, calltype, callfrom, callto, ring, talk, phone, account, costcurrency, costregion, reason, special, extras, provoiceid, callid, agentid, talkgroup, cost, sourcedate)values ( 1, '02/18/16 09:11:24', 'Y', 2, '102', '4387', 9, 81, '07841269657', '{8D65BD48-5114-4', '£', 4, 0, 1234, '', '', '{522DBFD9-52D9-437A-B254-9498A92A854D}', '{8D65B', '', 0.00, '02/18/16 00:00:00')
go
SET NO_BROWSETABLE ON
go
SELECT SCOPE_IDENTITY() AS newID

go

Use `103` style to insert. `select convert(date,'31/08/2011 16:58:00',103)` — Pரதீப், Feb 18 '16 at 08:46
I am not in control of the inserting - this is the problem. I need to make the target (my sql server db over which I have full control) ready to cope with the format pumped into it. I believe it arrives in this format: 31/08/2011 16:58:00. — cs0815, Feb 18 '16 at 09:07
How are the dates stored in the Firebird server, because it looks like they are string values, and not `TIMESTAMP`. Or did you do a conversion yourself between getting the value from Firebird and sending it to SQL Server? — Mark Rotteveel, Feb 18 '16 at 12:25
@MarkRotteveel - not sure all I see are the traced insert statements which allowed me to adapt the database settings to us/english. — cs0815, Feb 18 '16 at 12:52

score 1 · Accepted Answer · edited May 23 '17 at 11:50

In your insert statement the date is '02/18/16 09:11:24' - note that the string is formatted as MM/dd/yyyy, and not dd/MM/yyyy as you suspected.
Therefore you need to change the date format of the server to MDY.
This can be done by setting either the language to us_english (that implicitly changes the date format to MDY), or the setting the date format directly to MDY.

If you can control the insert statement then use the 101 style for american date format:

convert(datetime, @datestring, 101) -- will work for @datestring = '02/18/16 09:11:24'

BTW, dates in sql server are not stored with the display format. that's why you get the sql-ansi standard format when you execute select getdate()

score 0 · Answer 2 · answered Feb 18 '16 at 09:56

How about creating a stored proc to insert into calls table? You could accept date as string (varchar) and then convert into desired datetime format inside the stored proc like this:

SELECT @calldateandtime = convert(smalldatetime, @calldateandtime, 103)

Date format issue SQL Server

2 Answers2