how to convert sqlserver UDF into CLR based function

Question

how to convert sqlserver UDF into CLR based function

arpitsinghal.tcs

SSC Veteran

Points: 216
More actions
August 31, 2011 at 11:48 pm

#390317

I need help in converting below function code in sql clr based function for data manipulation
Background: we need to load more than 10 gb of flat file daily and this require modification of date fields as well
Input data example: 20111012
============================================================================
Create FUNCTION [dbo].[USF_CONVERT_SAP_DATE_TO_SQL_DATE]
(
-- Add the parameters for the function here
@SAPDATE VARCHAR(250)
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @SQLDATE VARCHAR(250)
SET @SAPDATE = LTRIM(RTRIM(@SAPDATE))
IF LEFT(@SAPDATE,1) = '0' OR @SAPDATE = '' OR @SAPDATE IS NULL OR ISNUMERIC(@SAPDATE) = 0 OR LEN(@SAPDATE)<8
BEGIN
RETURN NULL
END
IF CAST(@SAPDATE AS BIGINT) < 17530101 OR CAST(@SAPDATE AS BIGINT) > 99993112
RETURN NULL
-- Add the T-SQL statements to compute the return value here
SELECT @SQLDATE = LEFT(@SAPDATE,4) + '-' + RIGHT(LEFT(@SAPDATE,6),2) + '-' + RIGHT(LEFT(@SAPDATE,8),2)
IF ISDATE(@SQLDATE) = 0
RETURN NULL
-- Return the result of the function
RETURN CAST(@SQLDATE AS DATETIME)
END
============================================================================
Code which i have written for the same is
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace SQLCLR
{
public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction()]
public static DateTime USF_CLR_CONVERT_SAP_DATE_TO_SQL_DATE(String SAP_DATE)
{
try
{
String SQLDATE = SAP_DATE.Trim();
String SQLLEFT = SQLDATE.Substring(0,1);
if ((SQLLEFT == "0") || (string.IsNullOrEmpty(SQLDATE) == true) || (SQLDATE.Length < 8))
{
return Convert.ToDateTime(DBNull.Value);
}
int d1 = Convert.ToInt32(SQLDATE);
if (( d1 < 17530101 || d1 > 99993112))
{
return Convert.ToDateTime(DBNull.Value);
}
string s1 = SQLDATE.Substring(0,4) + '-' + SQLDATE.Substring(4,2) + '-' + SQLDATE.Substring(6,2);
if (Convert.ToInt32(s1) == 0)
{
return Convert.ToDateTime(DBNull.Value);
}
return Convert.ToDateTime(s1);
}
catch (Exception ex)
{
throw ex;
}
}
}
}

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

nellisjp SSChasing Mays Points: 624 More actions · Answer 1

What would be the benefit of converting the UDF to a CLR? In my mind, I would just use a transform on the field(s) at load-time using the UDF (or just parse it in-line).

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

Noooooooooooooo!!!! 😉

You don't need to even have a UDF to do this. Just cast the 8 character input field as a DATETIME datatype and you're done!

As a side bar, if you import the data into a column defined as DATETIME, then SQL Server will automatically make the conversion.

If you're sure that there will be any bad dates or non-date data in the column, post back and I'll show you how to easily handle thiis in a very high performance manner without UDF's or {gasp!} a CLR for this simple task.

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

nellisjp SSChasing Mays Points: 624 More actions · Answer 3

Right, just a simple case statement like this (I'm sure this probably has plenty of holes, but it's not a half-bad place to start)

Select Case When Len(InputDate) <> 8 Then NULL

When IsNumeric(InputDate) = 0 Then NULL

When IsDate( SubString(InputDate,5,2)

+ '/' + Right(InputDate,2)

+ '/' + Left(InputDate,4) ) = 1 Then

cast( SubString(InputDate,5,2)

+ '/' + Right(InputDate,2)

+ '/' + Left(InputDate,4) as datetime )

Else NULL

End

From TableXYZ

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

nellisjp (9/2/2011)
Right, just a simple case statement like this (I'm sure this probably has plenty of holes, but it's not a half-bad place to start)
Select Case When Len(InputDate) <> 8 Then NULL
When IsNumeric(InputDate) = 0 Then NULL
When IsDate( SubString(InputDate,5,2)
+ '/' + Right(InputDate,2)
+ '/' + Left(InputDate,4) ) = 1 Then
cast( SubString(InputDate,5,2)
+ '/' + Right(InputDate,2)
+ '/' + Left(InputDate,4) as datetime )
Else NULL
End
From TableXYZ

Good code but you can get about twice the performance as well as some additional "date range" checking out of the following because it doesn't have to do so much work on strings...

SELECT CASE

WHEN InputDate LIKE '[12][0-29][0-9][0-9][01][0-9][0-3][0-9]'

AND ISDATE(InputDate) = 1

THEN CAST(InputDate AS DATETIME)

ELSE NULL

END

FROM #TableXYZ

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

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

There's one major problem that I see in all of this... [font="Arial Black"]why are we converting things that aren't dates into NULLs when the original data is SUPPOSED TO ONLY CONTAIN DATES??? [/font]:blink: Personally, I'd want to sequester such data and find out what happened before I allowed any of the file to come anywhere near a permanent table in any of my databases. 😉

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

nellisjp SSChasing Mays Points: 624 More actions · Answer 6

I suppose one could just put a check constraint on the source field based on the first part of case statement above. However, that probably wouldn't make any friends with the production guys :laugh: