CLR-addon for DATE and Split-String

Levente Kupás

0.00/5 (No votes)

3 Jan 2013CPOL1 min read

8.8K

CLR-addon for T-SQL DATE transformation and Split-String

Introduction

Many database programers confronted with the fact that the database contain date in different format: TIMESTAMP, LOCAL or UTC timezone and need some transformation between them. An another old SQL-kind problem is splitting the strings. By using this addon you can easily perform the above operations without losing any performance.

Background

Splitting the string is a CPU cost operation in T-SQL, moreover not any built-in function exists for it.
Using CLR and .Net is an obivious solution since there are many built-in functions for string operations.
Date transformations is also supported in .NET, so do not need to create any complicated date-transformation related functions in T-SQL (I meet with these and mostly they were wrong because of the daylight saving time).

Deploying the CLR

Basically you may deploy the CLR by running the Install.sql script or following the below steps. First enable the CLR, then simply create the assembly:

SQL

use [master]
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE; 
GO 

----------------------------
CREATE ASSEMBLY [Sql.Addons]  
FROM 'c:\SQLCLR\Sql.Addons.dll'
WITH PERMISSION_SET = SAFE
GO

Split - functions

I'm using this functions more targeted like usually because in this way I can eliminate the unnecessary CAST operation if I use the function in the join condition (CAST is also needed from NVARCHAR to VARCHAR).

SQL

-- nvarchar(max) string  -->  bigint
CREATE FUNCTION dbo.clr_split2bigint(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item bigint)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Bigint]
GO
grant select on dbo.clr_split2bigint to [public]
go

-- nvarchar(max) string  -->  int
CREATE FUNCTION dbo.clr_split2int(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item int)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Int]
GO
grant select on dbo.clr_split2int to [public]
go

-- nvarchar(max) string  -->  smallint
CREATE FUNCTION dbo.clr_split2smallint(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item smallint)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Smallint]
GO
grant select on dbo.clr_split2smallint to [public]
go

-- nvarchar(max) string  -->  nvarchar(4000) string
CREATE FUNCTION dbo.clr_split2string(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item nvarchar(4000))
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2String]
GO
grant select on dbo.clr_split2string to [public]
go

-- nvarchar(max) - string  -->  nvarchar(max) - string
CREATE FUNCTION dbo.clr_split2max(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item nvarchar(max))
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Max]
GO
grant select on dbo.clr_split2max to [public]
go

Example:

SQL

-- nvarchar(max) string  -->  bigint
select * from dbo.clr_split2bigint('1,2,3,4, 1000000000000', ',')

-- nvarchar(max) string  -->  int
select * from dbo.clr_split2int('1,2,3,4, 1000000000', ',')

-- nvarchar(max) string  -->  smallint
select * from dbo.clr_split2smallint('1,2,3,4, 5', ',')

-- nvarchar(max) string  -->  nvarchar(4000) string
select * from dbo.clr_split2string('1,2,3,4, 5', ',')

-- nvarchar(max) - string  -->  nvarchar(max) - string
select * from dbo.clr_split2max('1,2,3,4, 5', ',')

DATE- functions

Below listed many transformations between local-time, utc-time, timestamp and timetick.

* --> UTC

SQL

--------------------
-- * --> utc-time --
--------------------

-- local-time --> utc-time
CREATE FUNCTION dbo.clr_date_localtime2utc(@date datetime)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToUtc]
GO
grant execute on dbo.clr_date_localtime2utc to [public]
go

-- utc-timestamp --> utc-time
CREATE FUNCTION dbo.clr_date_utcts2utctime(@timestamp bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TimeStampToUtcTime]
GO
grant execute on dbo.clr_date_utcts2utctime to [public]
go

-- local-timetick --> utc-time
CREATE FUNCTION dbo.clr_date_localticks2utctime(@ticks bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TicksToUtcTime]
GO
grant execute on dbo.clr_date_localticks2utctime to [public]
go



-------------------------
-- * --> utc-timestamp --
-------------------------

-- local-time --> utc-timestamp
CREATE FUNCTION dbo.clr_date_localtime2utcts(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToTimeStamp]
GO
grant execute on dbo.clr_date_localtime2utcts to [public]
go

-- utc-time --> utc-timestamp
CREATE FUNCTION dbo.clr_date_utctime2utcts(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcTimeToTimeStamp]
GO
grant execute on dbo.clr_date_utctime2utcts to [public]
go



-------------------------
-- * --> utc-timeticks --
-------------------------

-- utc-timeticks
CREATE FUNCTION dbo.clr_date_utcticks()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[GetUtcTicks]
GO
grant execute on dbo.clr_date_utcticks to [public]
go

-- local-time --> utc-timetick
CREATE FUNCTION dbo.clr_date_localtime2utcticks(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcTimeToTicks]
GO
grant execute on dbo.clr_date_localtime2utcticks to [public]
go

* --> Local-Time

SQL

----------------------
-- * --> local-time --
----------------------

-- utc-time --> local-time
CREATE FUNCTION dbo.clr_date_utc2localtime(@date datetime)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcToLocalTime]
GO
grant execute on dbo.clr_date_utc2localtime to [public]
go

-- utc-timestamp --> local-time
CREATE FUNCTION dbo.clr_date_utcts2localtime(@timestamp bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TimeStampToLocalTime]
GO
grant execute on dbo.clr_date_utcts2localtime to [public]
go

-- utc-timetick --> local-time
CREATE FUNCTION dbo.clr_date_utcticks2localtime(@ticks bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TicksToLocalTime]
GO
grant execute on dbo.clr_date_utcticks2localtime to [public]
go



---------------------------
-- * --> local-timeticks --
---------------------------

-- local-timeticks
CREATE FUNCTION dbo.clr_date_localticks()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[GetLocalTimeTicks]
GO
grant execute on dbo.clr_date_localticks to [public]
go

-- utc-time --> local-timetick
CREATE FUNCTION dbo.clr_date_utctime2localticks(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToTicks]
GO
grant execute on dbo.clr_date_utctime2localticks to [public]
go

Date --> String

SQL

CREATE FUNCTION dbo.clr_date_date2string(@date datetime)
RETURNS nvarchar(18)
AS
EXTERNAL NAME [Sql.Addons].[Date].[DateToString]
GO
grant execute on dbo.clr_date_date2string to [public]
go

Examples :

SQL

-- utc-time
select 'clr_date_localtime2utc' func, dbo.clr_date_localtime2utc(getdate()) utc_time
union
select 'clr_date_utcts2utctime', dbo.clr_date_utcts2utctime(dbo.clr_date_utctime2utcts(getutcdate())) utc_time
union
select 'clr_date_localticks2utctime', dbo.clr_date_localticks2utctime(dbo.clr_date_localticks()) utc_time

-- utc-timestamp
select 'clr_date_localtime2utcts' func, dbo.clr_date_localtime2utcts(getdate()) utc_timestamp
union
select 'clr_date_utctime2utcts', dbo.clr_date_utctime2utcts(getutcdate()) utc_timestamp

-- utc-timeticks
select 'clr_date_utcticks' func, dbo.clr_date_utcticks() utc_timetick
union 
select 'clr_date_localtime2utcticks', dbo.clr_date_localtime2utcticks(getdate())

-- local-time
select 'clr_date_utc2localtime' func, dbo.clr_date_utc2localtime(getutcdate()) local_time
union
select 'clr_date_utcts2localtime', dbo.clr_date_utcts2localtime(dbo.clr_date_utctime2utcts(getutcdate())) local_time
union 
select 'clr_date_utcticks2localtime', dbo.clr_date_utcticks2localtime(dbo.clr_date_utcticks()) local_time

-- local-timeticks
select 'clr_date_localticks' func, dbo.clr_date_localticks() local_timetick
union
select 'clr_date_utctime2localticks', dbo.clr_date_utctime2localticks(getutcdate())

-- date --> string
select 'clr_date_date2string' func, dbo.clr_date_date2string(getdate()) date_string

Memory Usage

Use this method to force the system to try to reclaim the maximum amount of available memory and after that will returns the number of bytes allocated.

SQL

CREATE FUNCTION dbo.clr_addons_memory_usage()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Monitor].[MonitorMemoryUsage]
GO
grant select on dbo.clr_addons_memory_usageto [public]
go

Example

SQL

select dbo.clr_addons_memory_usage() bytes

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)