I was playing with some historical data (family tree) and i wanted to store data in sql server. When looking into family trees you reach the minimum datetime value of 1753-01-01 very soon. But .Net can save dates from 1.1.0001 on. So i went looking into using SQL CLR user defined datatype (UDT).
UDT's are interesting because you have to serialize them.
There are 3 ways of doing that:
- Format.Native
- Format.UserDefined
- Format.Unknown
When Format.Native is used you can only use blittable datatypes. Bol says these are:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, SqlBoolean
So that means no strings, no datatime, no reference datatypes of anykind.
That also means you have to use Format.UserDefined and implement IBinarySerialize interface. That's not realy hard and is evident in code.
I've also added standard date manipulation functions that work the same way as the ones native to Sql Server.
MaxByteSize must be specified so that the server know how much space can be allocated.
UTD's differ from standard datatype in that when selected they return their binary representation. You have to use ToString() method to display the proper value.
I've added some standard datetime formats for valid input but you can add more by simply adding them to the Common.DateTimeFormats array.
C# code:
DateTimeEx.cs
using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 200)]
public struct DateTimeEx : INullable, IBinarySerialize
{
private DateTime dateTimeEx;
private bool m_Null;
public override string ToString()
{
// Always return ISO Format
return dateTimeEx.ToString("yyyyMMdd HH:mm:ss:fff");
}
public bool IsNull
{
get { return (m_Null); }
}
public static DateTimeEx Null
{
get
{
DateTimeEx h = new DateTimeEx();
h.m_Null = true;
return h;
}
}
public static DateTimeEx Parse(SqlString s)
{
if (s.IsNull)
return Null;
DateTimeEx u = new DateTimeEx();
// add more formats if you wish
u.dateTimeEx = DateTime.ParseExact(s.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces);
return u;
}
/// <summary>
/// Converts the datetime to SqlDateTime. if DateTimeEx is less than SqlDateTime it returns 17530101
/// </summary>
/// <returns>DateTimeEx converted to SqlDateTime </returns>
public SqlDateTime ConvertToSqlDateTime()
{
if (dateTimeEx < new DateTime(1753, 1, 1))
return new SqlDateTime(1753, 1, 1);
else
return new SqlDateTime(dateTimeEx.Year, dateTimeEx.Month, dateTimeEx.Day,
dateTimeEx.Hour, dateTimeEx.Minute, dateTimeEx.Second,
dateTimeEx.Millisecond);
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
dateTimeEx = new DateTime(r.ReadInt64());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(dateTimeEx.Ticks);
}
#endregion
}
DateTimeExFunctions.cs
using System;
using System.Globalization;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class DateTimeUDFs
{
[SqlFunction]
public static DateTimeEx DateAddEx(SqlString datePart, int number, DateTimeEx date)
{
datePart = datePart.Value.ToLower();
DateTime dt = DateTime.ParseExact(date.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces);
if (datePart == "dd" || datePart == "d")
dt = dt.AddDays(number);
else if (datePart == "yy" || datePart == "yy")
dt = dt.AddYears(number);
else if (datePart == "qq" || datePart == "q")
dt = dt.AddMonths(number * 3);
else if (datePart == "mm" || datePart == "m")
dt = dt.AddMonths(number);
else if (datePart == "dy" || datePart == "y")
dt = dt.AddDays(number);
else if (datePart == "wk" || datePart == "ww")
dt = dt.AddDays(number * 7);
else if (datePart == "dw" || datePart == "w")
dt = dt.AddDays(number);
else if (datePart == "hh")
dt = dt.AddHours(number);
else if (datePart == "mi" || datePart == "n")
dt = dt.AddMinutes(number);
else if (datePart == "ss" || datePart == "s")
dt = dt.AddSeconds(number);
else if (datePart == "ms")
dt = dt.AddMilliseconds(number);
return DateTimeEx.Parse(dt.ToString("yyyyMMdd HH:mm:ss:fff"));
}
[SqlFunction]
public static SqlInt32 DateDiffEx(SqlString datePart, DateTimeEx startDate, DateTimeEx endDate)
{
datePart = datePart.Value.ToLower();
DateTime dtStart = DateTime.ParseExact(startDate.ToString(),
Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces);
DateTime dtEnd = DateTime.ParseExact(endDate.ToString(),
Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces);
SqlInt32 diff = 0;
TimeSpan TS = new TimeSpan(dtEnd.Ticks - dtStart.Ticks);
if (datePart == "dd" || datePart == "d")
diff = TS.Days;
else if (datePart == "yy" || datePart == "yy")
diff = (int)(TS.Days / 365);
else if (datePart == "qq" || datePart == "q")
// simple quarter difference calcualtion
diff = (int)((TS.TotalDays / 365) * 4);
else if (datePart == "mm" || datePart == "m")
// simple month difference calcualtion
diff = (int)((TS.TotalDays / 365) * 12);
else if (datePart == "dy" || datePart == "y")
diff = TS.Days;
else if (datePart == "wk" || datePart == "ww")
diff = TS.Days;
else if (datePart == "dw" || datePart == "w")
diff = TS.Days / 7;
else if (datePart == "hh")
diff = TS.Hours;
else if (datePart == "mi" || datePart == "n")
diff = TS.Minutes;
else if (datePart == "ss" || datePart == "s")
diff = TS.Seconds;
else if (datePart == "ms")
diff = TS.Milliseconds;
return diff;
}
[SqlFunction]
public static SqlInt32 DatePartEx(SqlString datePart, DateTimeEx date)
{
datePart = datePart.Value.ToLower();
DateTime dt = DateTime.ParseExact(date.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces);
int retValue = 0;
if (datePart == "dd" || datePart == "d")
retValue = dt.Day;
else if (datePart == "yy" || datePart == "yy")
retValue = dt.Year;
else if (datePart == "qq" || datePart == "q")
{
int quarter = dt.Month / 3;
retValue = (quarter == 0 ? 1 : quarter);
}
else if (datePart == "mm" || datePart == "m")
retValue = dt.Month;
else if (datePart == "dy" || datePart == "y")
retValue = dt.DayOfYear;
else if (datePart == "wk" || datePart == "ww")
// get the week of year with the rule that the week starts on the first day of week
// and the first day of week is monday
retValue = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(dt, CalendarWeekRule.FirstDay,
DayOfWeek.Monday);
else if (datePart == "dw" || datePart == "w")
retValue = (int)dt.DayOfWeek + 1; // .net returns 0-6 SQL server returns 1-7
else if (datePart == "hh")
retValue = dt.Hour;
else if (datePart == "mi" || datePart == "n")
retValue = dt.Minute;
else if (datePart == "ss" || datePart == "s")
retValue = dt.Second;
else if (datePart == "ms")
retValue = dt.Millisecond;
return retValue;
}
[SqlFunction]
public static SqlInt32 YearEx(DateTimeEx date)
{
return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces).Year;
}
[SqlFunction]
public static SqlInt32 MonthEx(DateTimeEx date)
{
return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces).Month;
}
[SqlFunction]
public static SqlInt32 DayEx(DateTimeEx date)
{
return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowWhiteSpaces).Day;
}
};
Common.cs
public static class Common
{
// add more formats if you wish
public static readonly string[] DateTimeFormats = new string[] {
"yyyyMMdd HH:mm:ss:fff", "yyyyMMdd",
"yyyy-MM-dd HH:mm:ss:fff", "yyyy-MM-dd",
"yyyy/MM/dd HH:mm:ss:fff", "yyyy/MM/dd",
"yyyyMMdd HH:mm:ss", "yyyy-MM-dd HH:mm:ss",
"yyyy/MM/dd HH:mm:ss"
};
}
SQL code:
USE master
go
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
go
CREATE DATABASE testCLR
go
ALTER DATABASE testCLR SET TRUSTWORTHY ON
go
USE testCLR
go
CREATE ASSEMBLY DateTimeEx
FROM 'D:\Test\DateTimeEx.dll'
WITH PERMISSION_SET = SAFE
go
CREATE TYPE dbo.DateTimeEx
EXTERNAL NAME DateTimeEx.[DateTimeEx];
go
-- WITH RETURNS NULL ON NULL INPUT means that if any of the input arguments is null
-- the function doesn't execute so the result of the call is null
CREATE FUNCTION dbo.DateAddEx(@datePart NVARCHAR(3), @number INT, @date DateTimeEx)
RETURNS DateTimeEx
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DateAddEx
go
CREATE FUNCTION dbo.DateDiffEx(@datePart NVARCHAR(3), @startDate DateTimeEx, @endDate DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DateDiffEx
go
CREATE FUNCTION dbo.DatePartEx(@datePart NVARCHAR(3), @date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DatePartEx
go
CREATE FUNCTION dbo.YearEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.YearEx
go
CREATE FUNCTION dbo.MonthEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.MonthEx
go
CREATE FUNCTION dbo.DayEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DayEx
GO
-- let's test it if it works
CREATE TABLE TestUDT( date DateTimeEx)
GO
INSERT INTO TestUDT
SELECT '16500101'
INSERT INTO TestUDT
SELECT '16510101'
INSERT INTO TestUDT
SELECT '11500101'
INSERT INTO TestUDT
SELECT '09500101'
INSERT INTO TestUDT
SELECT NULL
INSERT INTO TestUDT
SELECT '11500326'
SELECT date, -- this displays types byte array
date.ToString(), -- so we must use the ToString() to display it properly
dbo.DateAddEx('d', 5, date).ToString(),
dbo.DateDiffEx('yy', date, CONVERT(VARCHAR(36), GETDATE(), 120)),
dbo.DatePartEx('dw', date),
dbo.YearEx(date),
dbo.MonthEx(date),
dbo.DayEx(date)
FROM TestUDT
go
DROP FUNCTION DateAddEx
DROP FUNCTION DateDiffEx
DROP FUNCTION DatePartEx
DROP FUNCTION YearEx
DROP FUNCTION MonthEx
DROP FUNCTION DayEx
DROP TABLE TestUDT
DROP TYPE dbo.DateTimeEx
DROP ASSEMBLY DateTimeEx
go
USE master
DROP DATABASE testCLR
| |
|
|
