The date type is not implemented in sqlserver2005, but it provides good scalability. You can use CLR to construct the date type. Some of them are written based on the Fc code.
The date type is not implemented in SQL server2005, but it provides good scalability. You can use CLR to construct the date type. Some of them are written based on the Fc code.
/***********************************
Author: trieagle (to show you the shadow wall)
Date: 2009.8.14
Note: Reprinted please keep this information
************************************/
Use c # To construct the date data type
The date type is not implemented in SQL server2005, but it provides good scalability. You can use CLR to construct the date type. Some of them are written based on the Fc code.
Steps:
1. Create a project in vs 2005, select c # --> database --> SQL server project, and enter the project name.
2. Select the database to connect
3. Right-click the project name, and choose add> Create item> User-Defined type> enter type name.
4. The Code is as follows:
The Code is as follows:
Using System;
Using System. Data;
Using System. Data. SqlClient;
Using System. Data. SqlTypes;
Using Microsoft. SqlServer. Server;
[Serializable]
[Microsoft. SqlServer. Server. SqlUserDefinedTypeFormat. UserDefined, IsByteOrdered = true, MaxByteSize = 20, ValidationMethodName = "ValidateDate")]
Public struct date: INullable, IBinarySerialize
{
// Private member
Private bool m_Null;
Private string m_date;
Public override string ToString ()
{
If (this. m_Null)
Return "null ";
Else
{
Return this. m_date;
}
}
Public bool IsNull
{
Get
{
Return m_Null;
}
}
Public static date Null
{
Get
{
Date h = new date ();
H. m_Null = true;
Return h;
}
}
Public static date Parse (SqlString s)
{
If (s. IsNull | (! S. IsNull & s. Value. Equals ("")))
Return Null;
Else
{
Date u = new date ();
String [] xy = s. Value. Split ("". ToCharArray ());
U. m_date = xy [0];
If (! U. ValidateDate ())
Throw new ArgumentException ("invalid time ");
Return u;
}
}
Public string _ date
{
Get
{
Return this. m_date;
}
Set
{
M_Null = true;
M_date = value;
If (! ValidateDate ())
Throw new ArgumentException ("invalid time ");
}
}
Public void Write (System. IO. BinaryWriter w)
{
Byte header = (byte) (this. IsNull? 1: 0 );
W. Write (header );
If (header = 1)
{
Return;
}
W. Write (this. m_date );
}
Public void Read (System. IO. BinaryReader r)
{
Byte header = r. ReadByte ();
If (header = 1)
{
This. m_Null = true;
Return;
}
This. m_Null = false;
This. m_date = r. ReadString ();
}
Private bool ValidateDate () // determines whether the time is valid
{
Try
{
DateTime dt = Convert. ToDateTime (m_date );
Return true;
}
Catch
{
Return false;
}
}
}
5. Press F5 to deploy
6. test:
The Code is as follows:
Create table tb (id int, dt dbo. Date default convert (dbo. Date, CONVERT (VARCHAR (10), GETDATE (), 120 )));
Insert into tb (id) values (1)
SELECT id, dt = dt. ToString () FROM tb;
/*
Result:
Id dt
1 2009-08-14
*/
Drop table tb;
Note:
1. If you want to add or subtract a date from the date type, you can call ToString () to output it as a string, convert it to the datetime type, and then calculate the date.
2. You cannot directly use select * from tb to output the values of the dt column. In this way, the output is a string of binary numbers.
