August 31, 2011 at 11:48 pm
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;
}
}
}
}
September 2, 2011 at 7:20 am
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).
September 2, 2011 at 2:33 pm
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
Change is inevitable... Change for the better is not.
September 2, 2011 at 2:51 pm
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
September 2, 2011 at 6:29 pm
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
Change is inevitable... Change for the better is not.
September 2, 2011 at 6:32 pm
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
Change is inevitable... Change for the better is not.
September 3, 2011 at 2:40 pm
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:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply