Stored Procedures
A problem with a transition filetime has been encountered in a recent project. Time is in C # in the Datetime.tofiletime write to the database, which in the stored procedure to determine the year and quarter became a problem, how to convert time into a normal time format? There is no fromfiletime in SQL Server? In fact, it is very simple to write a DLL for conversion time in C # and then call it in the stored procedure.
The classes for converting time are as follows:
[Guid ("729ba6af-3eff-4b75-b43b-d951a190dbe6")]
public class Filetimeconvert
{
Public Filetimeconvert ()
{
}
public string ConvertTime (long filetime)
{
String retVal = String. Empty;
if (filetime > 0)
{
DateTime dt = Datetime.fromfiletime (FILETIME);
RetVal = dt. ToString ();
}
return retVal;
}
}
Note that this GUID we reference in the stored procedure to use this GUID.
For convenience it may be necessary to call in multiple places, we specialize in writing a stored procedure
As follows:
CREATE PROCEDURE Spu_convertfiletimetonormal
@ticks BIGINT,
@returnval varchar () output
As
DECLARE @src varchar (255)
DECLARE @desc varchar (255)
DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate ' {729ba6af-3eff-4b75-b43b-d951a190dbe6} ', @object out
If @hr = 0--If the creation of the object succeeds
BEGIN
EXEC @hr = sp_OAMethod @object, ' ConvertTime ', @returnval out, @ticks
Print @returnval
Print @hr
if (@hr <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @object, @src out, @desc out
SELECT Hr=convert (varbinary (4), @hr), source= @src, description= @desc
End
End
ELSE
BEGIN
--print @hr
print ' Create Object converttime failed '
EXEC sp_OAGetErrorInfo @object, @src out, @desc out
SELECT Hr=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
Return
End
Go
This time we're still in the final step. Register DLL
Enter Regasm-codebase path under the. NET command line
OK, now we can call this stored procedure in other stored procedures to return to normal time.
Give a small example:
EXEC Spu_convertfiletimetonormal @CreateTime, @NormalTime out
@CreateTime: FILETIME Format
@NormalTime: Normal time format string (Spu_convertfiletimetonormal stored procedure return value)
This simple little example illustrates how we call a DLL in a stored procedure
Do you have one more choice to solve the problem?