Get column values by comma separated in Sql Server

Get column values by comma separated in Sql Server
Introduction : In this article i will explain you how to get column's values by comma separated in sql server .
It is very easy to get all values of column in one string as separated by comma .

Query for get comma separated value of column in sql server :

Declare @Description varchar(4000)
select @Description = coalesce(@Description + ',' , '') + Name FROM UserTable
print @Description 

Output of query :

seta hamid , bhaumik vora ,vinayak , dave 

Related Other posts

Html Code in blog Radiobuttonlist Example Image In Datalist Merge docs files in c#

Database backup in sql Server 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

All Sundays between two dates in asp.net C#

Find Sundays between two dates in asp.net C# .

Introduction : In this article i will show you how to get all Sundays between two dates in asp.net c# .

Following is function i have written for getting all Sundays between two dates in c# .It is very just you need to add code and you will able get all Sundays between two dates in c# . 

protected void Page_Load(object sender, EventArgs e)
{
    DateTime Dt1 = DateTime.Now;
    DateTime Dt2 = DateTime.Now.AddDays(30);
    GetAllSundays(Dt1, Dt2);
}
public void GetAllSundays(DateTime Date1, DateTime Date2)
{
    TimeSpan DateDiff = Date2.Subtract(Date1);
    for (int i = 0; i <= DateDiff.Days; i++)
    {
        if (Date1.Date.AddDays(i).DayOfWeek == DayOfWeek.Sunday)
        {
         Response.Write(Date1.Date.AddDays(i).ToLongDateString() + " " + Date1.Date.AddDays(i).DayOfWeek + "
");
        }
    }
}

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

Find week number from Date in asp.ne c#

Find the week from the date in asp.net c# :

Introduction : This article describe you how to find week number from date in c# .I have created one function which return you week number from the date in c#  .

C# Code for finding the week number :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace HamidSite
{
    public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {            
            int WeekNo = GetWeeknumber(DateTime.Now);
        }
        public int GetWeeknumber(DateTime Date)
        {
            try
            {   int iMonth = Date.Month;
                int returnNumber = 0;
                for (int i = 1; i <= 5; i++)
                {
                    if (iMonth == Date.AddDays(-7 * i).Month)
                    { }
                    else
                    {
                        returnNumber = i; break;
                    }
                }
                return returnNumber;  
            }
            catch (Exception Exc) { throw Exc; }
        }
    }
}

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 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