CODEWITHSUNDEEP
sql-serversql-server-2008firebird
cs0815
cs0815

Reputation: 17408

Date format issue SQL Server

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

Upvotes: 2

Views: 595

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82484

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()

Upvotes: 1

Maaykel
Maaykel

Reputation: 31

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)

Upvotes: 0

Related Questions