CODEWITHSUNDEEP
sql-serversql-server-2008
user394307
user394307

Reputation:

Identical query with same data which uses DATEDIFF fails in one and passes in one

I have two queries 

use [DatabaseAA]
select *
,DATEDIFF(MINUTE,SomeDate,'3/28/2012 12:52:25  PM +00:00')

This passes, while the same query when executed against another database fails.

use [DatabaseBB]
select *
,DATEDIFF(MINUTE,SomeDate,'3/28/2012 12:52:25  PM +00:00')

fails in the DatabseBB

Both the sometime has same values. It is failing for the 3 parameter to the datediff. Any ideas why this is the case ?

The error message is 

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 200

Answers (2)

Bridge
Bridge

Reputation: 30711

Are both the SQL servers using the same date format?

You can see the current setting with DBCC USEROPTIONS and set it if necessary with SET DATEFORMAT (documentation for that is here).

Upvotes: 2

Alex K.
Alex K.

Reputation: 175876

That will fail if the database's compatibility level is 80 or 90 (SQL 2000/5) you need to set it to 100 for SQL 2008.

Upvotes: 0

Related Questions