Simons SQL Blog

Dates don’t work when copied from SQL Profiler - Error converting data type varchar to datetime

If you are running with British english as your language or some other language with a different date format to the guys in the US then you will have come across this handy error.

This happens when you try and copy statements from RPC Events in SQL Server Profiler into Management Studio (or other tool) and try and run then.

But you say, it shouldn’t happen then statement ran fine the first time. That is may well be the case.

The problem arises because for date parameters an RPC call passes the value as a binary value and not a string representation. However if profiler displayed a binary value that would be a bit unhelpful and so it displays a string representation, and there’s the problem. Once you start bringing strings into the equation you get into localisation issues unless you use a standard date format.

The problem is that SQL Profiler has chosen the ANSI date format, and unfortunately SQL Server doesn’t treat ANSI dates properly, it allows the DATEFORMAT to affect the parsing of them. It shouldn’t but it does.

Interestingly if your parameters are the new datetime2 or date date types then there isn’t a problem because the SQL team fixed the parsing of ANSI dates but only for the new date types. You can replicate the issues by running this code

use tempdb

go

create procedure foo @a datetime as select @a

go

set dateformat dmy--British English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

go

set dateformat mdy--English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

The first execution of foo will fail but the second one will work. If we change the datatype for parameter @a it will work fine.

use tempdb

go

create procedure foo @a datetime2 as select @a

go

set dateformat dmy--British English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

go

set dateformat mdy--English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

As an aside the error you get will be different to a normal datetime arsing error because it is being done by a different bit of code in SQL Server. A normal date parsing error would be something like

Msg 242, Level 16, State 3, Line x

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

Getting a fix

What you need is the dates in a standard that works you can use either of the ISO formats yyyy-mm-ddThh:mi:ss.mmm or yyyymmdd ddThh:mi:ss.mmm.

The fix would be to get Profiler to output the correct dates. You might dig about and find a regional settings option but that seems to do nada. A connect item has already been raised for this and is found here https://connect.microsoft.com/SQLServer/feedback/details/538980/profiler-date-format?wa=wsignin1.0 please vote on this if you use languages with non mdy formats.

However there is no fix currently, and to be honest I don’t see one coming.

In frustration I decided to write a little app to do it that uses regex to replace the dates. I’ve used this regex to find the dates

and then these to replace the dates

This happens to another issue that occurs which is profiler outputs the microseconds part of dates which isn’t valid for the older dates. I think that is fixed but I’ve included a fix using the second replace anyway.

The exe which is attached can be run as a systray or from the command line to replace a file.

SimonSays <action> <filename>

<action> can be one of ANSI2ISO or ANSI2ISO_NO_MICRO

The latter means it can used as an external tool in Management Studio, but does require the file to be saved.

The systray option converts what ever is in the clipboard