Click here to Skip to main content
Click here to Skip to main content

CLR-addon for DATE and Split-String

, 3 Jan 2013
Rate this:
Please Sign up or sign in to vote.
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:  

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

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

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

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

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

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 : 
-- 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. 

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 

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)

About the Author

Levente Kupás
Database Developer
Hungary Hungary
2010-2012 - DB. Architect & Senior DB. Developer - www.livejasmin.com
2008-2010 - Senior DB. Developer - www.logmein.com
2006-2008 - DBA & DB. Developer - www.eurotaxglass.co.uk

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 3 Jan 2013
Article Copyright 2013 by Levente Kupás
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid