Convert integer in time In Sql Server

Convert Integer in time In Sql Server

Introduction : In this article i will show you how to convert Integer to time format in sql server .I have created function will return time as a string .Its takes parameter as a second in integer and return time .

CREATE function [dbo].[GetMinutesFromInt](@seconds int)  
returns  varchar(20)  
as
 begin  
   declare @time varchar(20)  
   set @time =    CASE WHEN @seconds/3600<10 THEN '0' ELSE '' END   + RTRIM(@seconds/3600)  + ':' + RIGHT('0'+RTRIM((@seconds % 3600) / 60),2)  + ':' + RIGHT('0'+RTRIM((@seconds % 3600) % 60),2)  
   return(@time) 
 end 

Call Function :

select dbo.GetMinutesFromInt(70)

Output :

00:01:10

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Convert dateTime in format date time using Convert Function

Convert dateTime in format date time using Convert Function

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Following are the Date Time format outputs if you pass related parameter to Convert function




-------------------------------------------------------------------------------------
Date OutPut
-------------------------------------------------------------------------------------

select convert( varchar , getdate() , 101 ) mm / dd / yyyy
select convert( varchar , getdate() , 102 ) yy.mm.dd
select convert( varchar , getdate() , 103 ) dd/mm/yy
select convert( varchar , getdate() , 104 ) dd.mm.yy
select convert( varchar , getdate() , 105 ) dd-mm-yy
select convert( varchar , getdate() , 106 ) dd mon yy
select convert( varchar , getdate() , 107 ) Mon dd, yy
select convert( varchar , getdate() , 108 ) hh:mm:ss
select convert( varchar , getdate() , 109 ) mon dd yyyy hh:mi:ss:mmmAM (or PM)
select convert( varchar , getdate() , 110 ) mm-dd-yy
select convert( varchar , getdate() , 111 ) yy/mm/dd
select convert( varchar , getdate() , 112 ) yymmdd
select convert( varchar , getdate() , 113 ) dd mon yyyy hh:mm:ss:mmm(24h)
select convert( varchar , getdate() , 114 ) hh:mi:ss:mmm(24h)

--------------------------------------------------------------------------------------

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Cursor In Sql Server

Cursor In Sql Server

Cursor is a database Object used to manipulate data on a row-by-row basis ..

Following are step to create Cursor .

Declaring a Cursor
Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-Cursur standard and a syntax using a set of Transact-SQL extensions.
This is SQL-Cursur Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

Following is Table-Value Function In which I have Used Cursor ::

FUNCTION [dbo].[fntbGetRevenueOfEmployees]()
RETURNS @EmployeeRevenue TABLE ( OrderDate Date , EmployeeID int ,Employee1 decimal(16,2) , Employee2 decimal(16,2), Employee3 decimal(16,2) ,Employee4 decimal(16,2)  , 
Employee5 decimal(16,2)   )
AS
BEGIN        
DECLARE  Name_Cursor CURSOR FOR 
Select  convert( varchar , CustomerOrders.OrderDate ,101)  ,         Employees.EmployeeID , sum(dbo.fnItemPriceTotal(customerorderDetails.Qty        ,customerorderDetails.Price,ISNULL(customerorderDetails.Double1,0))) as TotalForRow From  CustomerOrders
left join customerorderDetails on CustomerOrders.OrderID = customerorderDetails.OrderID
left join Accounts on Accounts.AccountID =  CustomerOrders.CustomerID
left join Employees ON Accounts.SalesPersonID = Employees.EmployeeID
group by convert( varchar , CustomerOrders.OrderDate ,101)  , Employees.EmployeeID
order by convert( varchar , CustomerOrders.OrderDate ,101)

DECLARE @C_OrderDate  as Datetime
DECLARE @C_OrderTotal as  decimal(16,2)
DECLARE @C_EmployeeID as Int 
Declare @intCount as int
declare @TempEmpId as int
set @intCount = 0

OPEN Name_Cursor;
FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;
WHILE @@FETCH_STATUS = 0
BEGIN
 
   insert into @EmployeeRevenue values( convert(varchar , @C_OrderDate ,101) , @C_EmployeeID , @C_OrderTotal , 0 ,0,0,0 ) ;

   FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;

END
close Name_Cursor  
deallocate Name_Cursor  
return  
end

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Convert seconds in time format HH:mm:ss in asp.net C#

Convert second in to time format HH:mm:ss in asp.net c# :

Introduction : In this article i will show you how to convert seconds in time format HH:mm:ss in asp.net c# .
It is very easy .I have used TimeSpan to get time format HH:mm:ss from seconds .

Code for Convert to seconds in time format HH:mm:ss in c# :

protected void Page_Load(object sender, EventArgs e)
{
    Response.Write(GetTimeFormat(1000)); // pass seconds here in integer
}
public string GetTimeFormat(int Seconds)
{
    string timeformat = string.Empty;
    TimeSpan Mytimes = new TimeSpan(0, 0, Seconds);
    timeformat = new DateTime(Mytimes.Ticks).ToString("HH:mm:ss");
    return timeformat;
}

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page