Passing a dateTime2 to a CLR Stored Procedure

• jm99

  SSCommitted

  Points: 1740

  More actions

  March 1, 2010 at 7:27 am

  #386411

  I'm trying to write a C# CLR stored procedure which needs to take a datetime2(3) as a parameter.

  However, mapping the parameter as a SqlDateTime causes the error:

  CREATE PROCEDURE for "myProcedure" failed because T-SQL and CLR types for parameter "@startDT" do not match.

  It appears that SqlDateTime still maps to the SQL DateTime accurate to 3.3 millisecs

  Any ideas appreciated as I'm now very confused.

• Sean Terry

  Old Hand

  Points: 353

  More actions

  March 1, 2010 at 9:41 pm

  #1126825

  jm99 (3/1/2010)

  ---

  It appears that SqlDateTime still maps to the SQL DateTime accurate to 3.3 millisecs

  SqlDateTime still maps to the SQL Server datetime data type, yes. If I remember correctly, I think the System.DateTimeOffset type will accept a datetime2 without complaint or loss of precision.

  E.g.:

  [Microsoft.SqlServer.Server.SqlFunction]

  [return: SqlFacet(MaxSize=21)]

  public static SqlString datetime2_test( DateTimeOffset datetime )

  {

  if ( datetime == null )

  return DateTime.Now.ToString( "yyyyMMddHHmmssfffffff" );

  else

  return datetime.ToString( "yyyyMMddHHmmssfffffff" );

  }

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  March 2, 2010 at 2:37 pm

  #1127434

  What is the CLR for? I mean, what is it supposed to do?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)