Convert datetime to string formate in ms sql

first create a function in sql

CREATE function NUMBERTOWORDS(@n bigint )
--Returns the number as words.
returns VARCHAR(255)
as
BEGIN
DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255)
SELECT @s=convert(varchar(20), @n)
SELECT @i=LEN(@s)
SELECT @result=''
WHILE (@i>0)
BEGIN
SELECT @temp=(SUBSTRING(@s,@i,1))
IF ((LEN(@s)-@i) % 3)=1
IF @temp='1'
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN 'ten'
WHEN '1' THEN 'eleven'
WHEN '2' THEN 'twelve'
WHEN '3' THEN 'thirteen'
WHEN '4' THEN 'fourteen'
WHEN '5' THEN 'fifteen'
WHEN '6' THEN 'sixteen'
WHEN '7' THEN 'seventeen'
WHEN '8' THEN 'eighteen'
WHEN '9' THEN 'nineteen'
END+' '+CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
WHEN ((LEN(@s)-@i)=19) THEN 'quintillion '
WHEN ((LEN(@s)-@i)=22) THEN 'sextillion '
WHEN ((LEN(@s)-@i)=25) THEN 'septillion '
WHEN ((LEN(@s)-@i)=28) THEN 'octillion '
WHEN ((LEN(@s)-@i)=31) THEN 'nonillion '
WHEN ((LEN(@s)-@i)=34) THEN 'decillion '
WHEN ((LEN(@s)-@i)=37) THEN 'undecillion '
WHEN ((LEN(@s)-@i)=40) THEN 'duodecillion '
WHEN ((LEN(@s)-@i)=43) THEN 'tredecillion '
WHEN ((LEN(@s)-@i)=46) THEN 'quattuordecillion '
WHEN ((LEN(@s)-@i)=49) THEN 'quindecillion '
WHEN ((LEN(@s)-@i)=52) THEN 'sexdecillion '
WHEN ((LEN(@s)-@i)=55) THEN 'septendecillion '
WHEN ((LEN(@s)-@i)=58) THEN 'octodecillion '
WHEN ((LEN(@s)-@i)=61) THEN 'novemdecillion '
ELSE ''
END+@result
ELSE
BEGIN
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END+' '+ CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
WHEN ((LEN(@s)-@i)=19) THEN 'quintillion '
WHEN ((LEN(@s)-@i)=22) THEN 'sextillion '
WHEN ((LEN(@s)-@i)=25) THEN 'septillion '
WHEN ((LEN(@s)-@i)=28) THEN 'octillion '
WHEN ((LEN(@s)-@i)=31) THEN 'nonillion '
WHEN ((LEN(@s)-@i)=34) THEN 'decillion '
WHEN ((LEN(@s)-@i)=37) THEN 'undecillion '
WHEN ((LEN(@s)-@i)=40) THEN 'duodecillion '
WHEN ((LEN(@s)-@i)=43) THEN 'tredecillion '
WHEN ((LEN(@s)-@i)=46) THEN 'quattuordecillion '
WHEN ((LEN(@s)-@i)=49) THEN 'quindecillion '
WHEN ((LEN(@s)-@i)=52) THEN 'sexdecillion '
WHEN ((LEN(@s)-@i)=55) THEN 'septendecillion '
WHEN ((LEN(@s)-@i)=58) THEN 'octodecillion '
WHEN ((LEN(@s)-@i)=61) THEN 'novemdecillion '
ELSE ''
END+@result
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'ten'
WHEN '2' THEN 'twenty'
WHEN '3' THEN 'thirty'
WHEN '4' THEN 'fourty'
WHEN '5' THEN 'fifty'
WHEN '6' THEN 'sixty'
WHEN '7' THEN 'seventy'
WHEN '8' THEN 'eighty'
WHEN '9' THEN 'ninety'
END+' '+@result
END
IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
BEGIN
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END +' '+CASE
WHEN (@s='0') THEN 'zero'
WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'hundred '
ELSE ''
END + CASE
WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
WHEN ((LEN(@s)-@i)=6) THEN 'million '
WHEN ((LEN(@s)-@i)=9) THEN 'billion '
WHEN ((LEN(@s)-@i)=12) THEN 'trillion '
WHEN ((LEN(@s)-@i)=15) THEN 'quadrillion '
WHEN ((LEN(@s)-@i)=18) THEN 'quintillion '
WHEN ((LEN(@s)-@i)=21) THEN 'sextillion '
WHEN ((LEN(@s)-@i)=24) THEN 'septillion '
WHEN ((LEN(@s)-@i)=27) THEN 'octillion '
WHEN ((LEN(@s)-@i)=30) THEN 'nonillion '
WHEN ((LEN(@s)-@i)=33) THEN 'decillion '
WHEN ((LEN(@s)-@i)=36) THEN 'undecillion '
WHEN ((LEN(@s)-@i)=39) THEN 'duodecillion '
WHEN ((LEN(@s)-@i)=42) THEN 'tredecillion '
WHEN ((LEN(@s)-@i)=45) THEN 'quattuordecillion '
WHEN ((LEN(@s)-@i)=48) THEN 'quindecillion '
WHEN ((LEN(@s)-@i)=51) THEN 'sexdecillion '
WHEN ((LEN(@s)-@i)=54) THEN 'septendecillion '
WHEN ((LEN(@s)-@i)=57) THEN 'octodecillion '
WHEN ((LEN(@s)-@i)=60) THEN 'novemdecillion '
ELSE ''
END+ @result
END
SELECT @i=@i-1
END
return REPLACE(@result,' ',' ')
END











GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

then  you can call like by sppilitting datetime

select dbo.NUMBERTOWORDS (4)
select dbo.NUMBERTOWORDS (31)
select dbo.NUMBERTOWORDS (2013)

or you can to i like

declare @Student_Birth_Date datetime =getdate();

declare @Student_AgeDay1 nvarchar(100)

declare @Student_AgeMonth1  nvarchar(100)

declare @Student_AgeYear1  nvarchar(100)

declare @Student_Birth_Wordday  nvarchar(100)

declare @Student_Birth_Wordmonth  nvarchar(100)

declare @Student_Birth_Wordyear  nvarchar(100)

declare @Student_Birth_Word  nvarchar(500)

set @Student_AgeDay1=(select DATEPART(DAY,@Student_Birth_Date))
set @Student_AgeMonth1=(select DATEPART(MONTH,@Student_Birth_Date))
set @Student_AgeYear1=(select DATEPART(YEAR,@Student_Birth_Date))
set @Student_Birth_Wordday=dbo.NUMBERTOWORDS (@Student_AgeDay1)
set @Student_Birth_Wordmonth=dbo.NUMBERTOWORDS (@Student_AgeMonth1)
set @Student_Birth_Wordyear=dbo.NUMBERTOWORDS (@Student_AgeYear1)
set @Student_Birth_Word=(@Student_Birth_Wordday+'-'+@Student_Birth_Wordmonth+'-'+@Student_Birth_Wordyear)

Read More

Update A Table from Another Table in MSSQL

Update A Table from Another Table in MSSQL

I use Two table  from which i have updated a single table from another table by matching a single column.


 update CourseWiseApplyStructureNEw  
set FeeHeadID=TS.SonetNewId 
from CourseWiseApplyStructureNEw CM inner join 
 FeeHead TS on CM.OLDFeeHeadID=TS.Head_Id

Read More

How to Create Cursor in MSSQL and Add Column as Row in Table

How to Create Cursor in MSSQL and Add Column as Row in Table from another table -:

Step-1 -:

 I create two table from in which
I have column to match
and 
Another table from which i want to match

I use cursor to match the column valum in another table row value.


create table #tmpHeads  
(  
HeadID nvarchar(100),  
HeadName nvarchar(100)  
) 
 declare @sumallheadwise nvarchar(max)=''  
declare @strQryHeadDetails nvarchar(max)
set @strQryHeadDetails=''  
declare @HDName nvarchar(50)  
declare @HDId nvarchar(50)  
  
insert into #tmpHeads select HeadId, HeadName from FeeHead  
declare cur cursor  for select HeadName, HeadID  from FeeHead where HeadID in (select FeeHeadID from FeeReciept_Master)  
--declare cur cursor  for select HeadName ,HeadId from #tmpHeads  
open cur fetch next from cur into  @HDName, @HDId  
while @@FETCH_STATUS=0  
begin  
exec ('alter table #tmpHeadsdetail add [' + @HDName + '] decimal(18,2) default 0.00 ' )  
  
if(@strQryHeadDetails='')  
begin  
set @strQryHeadDetails = @strQryHeadDetails + 'sum((case when FeeHeadID='''+@HDId +''' then RecievedAmount else 0 end )) as [' + @HDName +'] '  
set @sumallheadwise = @sumallheadwise + 'sum(['+@HDName +']) as [' + @HDName +'] '  
  
end  
else  
begin  
set @strQryHeadDetails = @strQryHeadDetails + ' , sum((case when FeeHeadID='''+@HDId +''' then RecievedAmount else 0 end )) as [' + @HDName +'] '  
set @sumallheadwise = @sumallheadwise + ', sum(['+@HDName +']) as [' + @HDName +'] '  
  
end  
  
fetch next from cur into  @HDName, @HDId  
end  

 --select @strQryHeadDetails 

close cur  
deallocate cur 


select * from #tmpHeads

Note- We always d
eallocate the Cursor but not drop the cursor
If you are running this code then first drop the temporary table from the memory as-

drop table  #tmpHeads

Read More

Make an Output parameter in Stored Procedure and Get it By Code In C#

Make an Output parameter in Stored Procedure and Get it By Code In C# -:

First -Create Stored Procedure (SP) in MS SQL SErver as-



create PROCEDURE DeletePayrollSetup   
(  
@Category nvarchar(20),   
@Head nvarchar(20),   
@NewHead nvarchar(50),   
@val int output  
)  
AS  
declare @chk int

  

set @chk = 0  

  

if exists(select * from salarycal where  category = @Category and formula like @NewHead )  

begin  

set @chk = 2 

end  

else if exists(select * from PayrollConditionallycalc where category = @Category and mainhead like @NewHead or ans like @NewHead)  

begin  

set @chk = 2

end  

else  

begin  

set @chk = 1

end  

set @val = @chk  

Now use this Sp in Our Code as-

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[((MasterForm)(this.ParentForm)).CnnStr].ConnectionString);

                            con.Open();

                            SqlCommand cmd = new SqlCommand("DeletePayrollSetup");

                            cmd.Parameters.AddWithValue("@Category", strCatId);

                            cmd.Parameters.AddWithValue("@Head", strHead);

                            cmd.Parameters.AddWithValue("@NewHead", strHeadIdNew);

                            cmd.Parameters.Add(new SqlParameter("@val", SqlDbType.Int));

                            cmd.Parameters["@val"].Direction = ParameterDirection.Output;

                            cmd.Connection = con;

                            cmd.CommandType = CommandType.StoredProcedure;

                            cmd.ExecuteNonQuery();

                            int Val = Convert.ToInt32(cmd.Parameters["@val"].Value);

                            con.Close();

This will hold the @val by giving its Direction

using this line-

 cmd.Parameters["@val"].Direction = ParameterDirection.Output;

and 

Then we hold a output value as-

  int Val = Convert.ToInt32(cmd.Parameters["@val"].Value);

Read More

How to Open Window Form in C#

First -Create a MDIParent form( this is master form which hold all child form)
Second -Create a windows form -for instance- PayrollSetupForm -
Then  open a MenuToolStrip by double clicking

Note-:
MdiParent - Master / Parent Form

Trick 1-To Open a Page

private void payrollSetupFormToolStripMenuItem_Click(object sender, EventArgs e)
        {
            PayrollSetupForm obj = new PayrollSetupForm();
            obj.MdiParent = this;
            obj.Show();
        }

Trick 2-To Open a Page

first create a method

 void OpenChildWindow(string FormName, Object NewFrm)

        {

            FormCollection AllForms = Application.OpenForms;

            foreach (Form frm in AllForms)

            {

                if (frm.Name == FormName)

                {

                    frm.Activate();

                    return;

                }

            }

            ((Form)NewFrm).MdiParent = this;

            ((Form)NewFrm).Show();

        }

then double click on a  menu Tool Strip

private void payrollSetupFormToolStripMenuItem_Click(object sender, EventArgs e)
        {

                FrmLedger1 ObjDef = new FrmLedger1();

            OpenChildWindow("FrmLedger", ObjDef);

       }

Read More

View All Table/Stored Procedure/Function/View in a single DataBase

View All Table in a single DataBase-:

If you want to check, how many table you have create in a single DataBase

select * from INFORMATION_SCHEMA.TABLES

 for this you will have to select a single database

View All Stored Procedure in a single DataBase-:

To check, how many Stored Procedure ,you have create in a single DataBase.

select * from INFORMATION_SCHEMA.ROUTINES

View All Stored Procedure in a single DataBase before or after date-:

To check, how many Stored Procedure ,you have create in a single DataBase.

select * from INFORMATION_SCHEMA.ROUTINES  where created<'10/31/2012'

select * from INFORMATION_SCHEMA.ROUTINES  where created>'10/31/2012'

The date formate is (mm/dd/yyyy ) formate.

View All Stored Procedure in a single DataBase before or after changed/altered Date-:

To check, how many Stored Procedure ,you have changed or altered by a single Date in a single DataBase.

select * from INFORMATION_SCHEMA.ROUTINES  where LAST_ALTERED>'10/31/2012'

select * from INFORMATION_SCHEMA.ROUTINES  where LAST_ALTERED<'10/31/2012'

The date formate is (mm/dd/yyyy ) formate.

Display All VIEW in a single DataBase -:

To check, how many VIEWS ,you have created  in a single DataBase


select * from INFORMATION_SCHEMA.VIEWS

Display All VIEW in a single DataBase before or after creation date-:

To check, how many VIEWS ,you have create in a single DataBase.

select * from INFORMATION_SCHEMA.VIEWS  where created<'10/31/2012'

select * from INFORMATION_SCHEMA.VIEWS  where created>'10/31/2012'

The date formate is (mm/dd/yyyy ) formate.

Display All VIEW in a single DataBase before or after changed/altered Date-:

To check, how many VIEWS , you have changed or altered by a single Date in a single DataBase.

select * from INFORMATION_SCHEMA.VIEWS  where LAST_ALTERED>'10/31/2012'

select * from INFORMATION_SCHEMA.VIEWS  where LAST_ALTERED<'10/31/2012'

The date formate is (mm/dd/yyyy ) formate.


NOTE-: If you want to check how many function you have created in a single database, then run a Stored Procedure Query to check funtion details

Read More

Send Date or DateTime from DatetimePicker

Send DateTime from DateTimePicker in a string formate in C#-:

after click on DateTime Picker we can convert its string format as-

 DateTMPicker.Value.Date.ToString();

Send Date but not Time from DateTimePicker in a string formate in C#-:

after click on DateTime Picker we can convert its string format as-

 DateTMPicker.Value.Date.ToString("dd/MM/yyyy");

Read More

How to check only one item in CheckListBox

How to check only one item in CheckListBox

I create a code but the problem is when it is taking two click for selecting a single index-

So for this code is following----------

        private void ChklstInstitute_ItemCheck(object sender, ItemCheckEventArgs e)

        {

            if (ChklstInstitute.CheckedItems.Count == 1)

            {

                Boolean isCheckedItemBeingUnchecked = (e.CurrentValue == CheckState.Checked);

                if (isCheckedItemBeingUnchecked)

                {

                    e.NewValue = CheckState.Checked;

                }

                else

                {

                    Int32 checkedItemIndex = ChklstInstitute.CheckedIndices[0];

                    ChklstInstitute.ItemCheck -= ChklstInstitute_ItemCheck;

                    ChklstInstitute.SetItemChecked(checkedItemIndex, false);

                    ChklstInstitute.ItemCheck += ChklstInstitute_ItemCheck;

                }

                return;

            }

        }

So its not working properly------

So develop a new code---

Generate a mouseclickevent on checklist-----

 private void ChklstInstitute_MouseClick(object sender, MouseEventArgs e)

        {

            int ij = ChklstInstitute.SelectedIndex;           

            for(int ii=0;ii<ChklstInstitute.Items.Count;ii++)

            {

                ChklstInstitute.SetItemChecked(ii,false);

            }

            //ChklstInstitute.SetItemChecked(ij,true);

            ChklstInstitute.SetSelected(ij, true);

        }

this code is working fine

Read More