??
It is not necessary for some composite types to perform paradigm decomposition, especially in the case of some unified models
??
DECLARE @i TimeBalance
SET @i = CAST(' 0/102 'asTimeBalance)
SELECT @i
SELECT @i. ToString()
SELECT @i. toTimeString()
SELECT @i. Distance()
SELECT @i. Distance2(' mi ')
??
SET @i = CAST(' 2015-1-1/2016-8-21 'asTimeBalance)
SELECT @i. ToString() string ,
@i. toTimeString() timestring ,
@i. Distance() Distance ,
@i. Distance2(' m ')distancemonth
??
??
??
?
Related information:
?
TSql vs. SQL CLR Performance Analysis
?
CLR-Integrated performance
Manipulating UDT Data
?
??
- <summary>
- Time Wallet Balance Type
- ///
- </summary>
- <remarks>
- Based on the long integer type, the structure is as follows:
- Start UTC seconds 4B, end UTC Seconds 4B
- </remarks>
- <see cref= "https://msdn.microsoft.com/en-us/library/ms131086.aspx"/>
- [Serializable]
- [Microsoft.SqlServer.Server.SqlUserDefinedType (Format.native)]
- Public struct Timebalance:inullable
- {
- ???? Public Override string ToString ()
- ???? {
- ??
- ???????? return string. Format ("{0}/{1}", Startsecond, Endsecond);
- ????}
- ??
- ???? Public string toTimeString ()
- ???? {
- ???????? return string. Format ("{0}/{1}", Utcsecondtodatetime (Startsecond). ToString ("yyyy-mm-dd HH:mm:ss"), Utcsecondtodatetime (Endsecond). ToString ("yyyy-mm-dd HH:mm:ss"));
- ????}
- ??
- ???? Public Static TimeBalance Parse (SqlString s)
- ???? {
- ???????? if (S.isnull)
- ???????????? return Null;
- ??
- ???????? TimeBalance u = new TimeBalance ();
- ???????? if (S.value.contains ("-"))
- ???????? {
- ???????????? U.startsecond = Datetimetoutcsecond (DateTime.Parse (S.value.split ('/') [0]));
- ???????????? U.endsecond = Datetimetoutcsecond (DateTime.Parse (S.value.split ('/') [1]));
- ????????} Else
- ???????? if (S.value.contains ("/"))
- ???????? {
- ???????????? U.startsecond = int. Parse (S.value.split ('/') [0]);
- ???????????? U.endsecond = int. Parse (S.value.split ('/') [1]);
- ????????}
- ???????? Else
- ???????? {
- ???????????? byte [] bs = bitconverter.getbytes (long. Parse (S.value));
- ???????????? U.startsecond = Bitconverter.toint32 (BS, 0);
- ???????????? U.endsecond = Bitconverter.toint32 (BS, 4);
- ??
- ????????}
- ???????? return u;
- ????}
- ??
- ??
- ??
- ??
- ???? Public BOOL IsNull
- ???? {
- ???????? Get
- ???????? {
- ???????????? //Place code here
- ???????????? return _null;
- ????????}
- ????}
- ??
- ???? Public Static TimeBalance Null
- ???? {
- ???????? Get
- ???????? {
- ???????????? TimeBalance h = new TimeBalance ();
- ???????????? H._null = true;
- ???????????? return H;
- ????????}
- ????}
- ??
- ??
- ???? Public Double Distance2 (SqlString type)
- ???? {
- ???????? Switch (Type. Value.tolower ())
- ???????? {
- ???????????? Case "y":
- ???????????????? return Utcsecondtodatetime (Endsecond). Year-utcsecondtodatetime (Startsecond). year;
- ???????????? Case "m":
- ???????????????? return Utcsecondtodatetime (Endsecond). Year * + utcsecondtodatetime (endsecond). Month-utcsecondtodatetime (Startsecond). Year * 12-utcsecondtodatetime (Startsecond). Month;
- ???????????? Case "D":
- ???????????????? return New TimeSpan (0, 0, 0, Endsecond-startsecond). Totaldays;
- ???????????? Case "h":
- ???????????????? return New TimeSpan (0, 0, 0, Endsecond-startsecond). TotalHours;
- ???????????? Case "mi":
- ???????????????? return New TimeSpan (0, 0, 0, Endsecond-startsecond). Totalminutes;
- ??
- ???????????? Case "s":
- ???????????? default:
- ???????????????? return Endsecond-startsecond;
- ????????}
- ????}
- ???? Public string Distance ()
- ???? {
- ???????? return New TimeSpan (0, 0, 0, Endsecond-startsecond). ToString ();
- ??
- ????}
- ???? #region get UTC
- ??
- ???? Public Static int Datetimetoutcsecond (datetime datetime)
- ???? {
- ???????? return (int) datetime.touniversaltime (). Subtract (UTC). TotalSeconds;
- ????}
- ??
- ???? Static DateTime utcsecondtodatetime (int second)
- ???? {
- ???????? return Utc. AddSeconds (second). ToLocalTime ();
- ????}
- ???? #endregion
- ??
- ???? //This is a placeholder member field
- ???? Public int Startsecond;
- ???? Public int Endsecond;
- ???? Public Static ReadOnly DateTime UTC = TIMEZONEINFO.CONVERTTIMETOUTC (new DateTime (1970, 1, 1), TIMEZONEINFO.UTC);
- ???? //Private member
- ???? Private bool _null;
- }
??
??
??
Using a user-defined type CLR UDT
