Here this script calculate total Sunday in give month and give year @month = 6 and @year = 2008.
declare @year int
declare @month int
declare @totaldays int
declare @isLeapYear bit
declare @firstDayofmonth varchar(20)
declare @date varchar(20)
set @isLeapYear = 'false'
--------------------------------------------------
--- Here change year and month for diff result
--------------------------------------------------
set @year = 2008
set @month = 6
--------------------------------------------------
set @totaldays = 31
set @date = cast(@year as varchar) + '-' + cast(@month as varchar) + '-01'
if(@year % 4 = 0)
set @isLeapYear = 'true'
if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'
if(@month = 1) set @totaldays = 31
if(@month = 2 )
begin
if(@isLeapYear = 'true') set @totaldays = 29
else set @totaldays = 28
end
if(@month = 3) set @totaldays = 31
if(@month = 4) set @totaldays = 30
if(@month = 5) set @totaldays = 31
if(@month = 6) set @totaldays = 30
if(@month = 7) set @totaldays = 31
if(@month = 8) set @totaldays = 31
if(@month = 9) set @totaldays = 30
if(@month = 10) set @totaldays = 31
if(@month = 11) set @totaldays = 30
if(@month = 12) set @totaldays = 31
set @firstDayofmonth = DATENAME(dw,@date)
print 'Given Year : ' + cast(@year as varchar)
print 'Given Month : ' + cast(@month as varchar)
print 'Total days : ' + cast(@totaldays as varchar)
print 'First day is : ' + @firstDayofmonth
declare @i int
declare @cnt int
declare @currday varchar(20)
set @i = 1
set @cnt = 0
set @currday = DATENAME(dw,@date)
while(@i <= @totaldays)
begin
if(@currday = 'Sunday') set @cnt = @cnt + 1
set @currday = DATENAME(dw,dateadd(dd,1,@date))
set @date = dateadd(dd,1,@date)
set @i = @i + 1
end
print 'Total Sunday in give month are : ' + cast(@cnt as varchar)
Output will be...
Given Year : 2008
Given Month : 6
Total days : 30
First day is : Sunday
Total Sunday in give month are : 5
About Me
-
Anil Desai.
Software Engineer
T M Technologies
જય વાળીનાથ - T-SQL is a basic of structure query language. So I always want to learn T-SQL in different way to get best performance in query. And its my passion.
Wednesday, April 30, 2008
Monday, April 14, 2008
Draw Ractangle Script in T-SQL
Here this script display ractangle in output window @size = 17.
Output will be...
declare @i int
declare @j int
declare @k int
declare @totLine int
declare @res varchar(100)
declare @showingCharacter varchar(1)
set @showingCharacter = '#'
set @totLine = 20
set @i = 1
set @j = 1
set @k = 1
set @res = ''
while(@k <= (@totLine))
begin
set @res = @res + '# '
set @k = @k + 1
end
print @res
set @res = ''
while(@i <= (@totLine - 2))
begin
set @res = '# ' + REPLICATE(' ',@totLine - 2) + '# '
set @i = @i + 1
print @res
end
print @res
set @res = ''
set @k = 1
while(@k <= (@totLine))
begin
set @res = @res + '# '
set @k = @k + 1
end
print @res
set @res = ''
# # # # # # # # # # # # # # # # # # # #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# # # # # # # # # # # # # # # # # # # #
Thursday, April 10, 2008
Table Copy Script in T-SQL
Script for copy records with table structure into new table from other existing table without creating new table
SELECT a.*
INTO dbo.newTable
FROM dbo.oldTable a
Script for copy only records into new table from other existing table.Here new table must be create before executing this script.
INSERT INTO dbo.newTable
SELECT Name,Address,Phone,Email_address
FROM dbo.oldTable
SELECT a.*
INTO dbo.newTable
FROM dbo.oldTable a
Script for copy only records into new table from other existing table.Here new table must be create before executing this script.
INSERT INTO dbo.newTable
SELECT Name,Address,Phone,Email_address
FROM dbo.oldTable
Cursor Script in T-SQL
Here this script show how to use Cursor...
declare @studentId int
declare @studentName varchar(50)
declare ibuffer cursor fast_forward for
select studentId,studentName
from Student
open ibuffer
fetch next from ibuffer into @studentId,@studentName
while(@@fetch_status!= -1)
begin
print 'Student Id : ' + cast(@studentId as varchar)
print 'Stuednt Name : ' + @studentName
fetch next from ibuffer into @studentId,@studentName
end
deallocate ibuffer
Output will be...
Student Id : 1
Stuednt Name : Anil Desai
Student Id : 2
Stuednt Name : Paresh Patel
Student Id : 3
Stuednt Name : Mitesh Modi
Student Id : 4
Stuednt Name : Nirmit Modi
Student Id : 5
Stuednt Name : Bharat Modi
declare @studentId int
declare @studentName varchar(50)
declare ibuffer cursor fast_forward for
select studentId,studentName
from Student
open ibuffer
fetch next from ibuffer into @studentId,@studentName
while(@@fetch_status!= -1)
begin
print 'Student Id : ' + cast(@studentId as varchar)
print 'Stuednt Name : ' + @studentName
fetch next from ibuffer into @studentId,@studentName
end
deallocate ibuffer
Output will be...
Student Id : 1
Stuednt Name : Anil Desai
Student Id : 2
Stuednt Name : Paresh Patel
Student Id : 3
Stuednt Name : Mitesh Modi
Student Id : 4
Stuednt Name : Nirmit Modi
Student Id : 5
Stuednt Name : Bharat Modi
Begin Transaction Script in T-SQL
Here this script show how to use Begin Transaction...
begin transaction
begin try
--...
--...
--Sql Statements
--...
--...
-- Here Successfully complite all above statements.
commit transaction
end try
begin catch
print 'Error No : ' + cast(ERROR_NUMBER() as varchar)
print 'Error Severity : ' + cast(ERROR_SEVERITY() as varchar)
print 'Error State : ' + cast(ERROR_STATE() as varchar)
print 'Error Line : ' + cast(ERROR_LINE() as varchar)
print 'Error Message : ' + cast(ERROR_MESSAGE() as varchar)
--Here if any statements create problem in executing than rollback
--all statement that executed before it.
rollback transaction
end catch
begin transaction
begin try
--...
--...
--Sql Statements
--...
--...
-- Here Successfully complite all above statements.
commit transaction
end try
begin catch
print 'Error No : ' + cast(ERROR_NUMBER() as varchar)
print 'Error Severity : ' + cast(ERROR_SEVERITY() as varchar)
print 'Error State : ' + cast(ERROR_STATE() as varchar)
print 'Error Line : ' + cast(ERROR_LINE() as varchar)
print 'Error Message : ' + cast(ERROR_MESSAGE() as varchar)
--Here if any statements create problem in executing than rollback
--all statement that executed before it.
rollback transaction
end catch
Monday, April 7, 2008
Try.....Catch Script in T-SQL
Here this script show how to use Try.....Catch Block in T-SQL.
begin try
print 1/0
print 'Sucessfully executed try block.'
end try
begin catch
print 'Error No : ' + cast(ERROR_NUMBER() as varchar)
print 'Error Severity : ' + cast(ERROR_SEVERITY() as varchar)
print 'Error State : ' + cast(ERROR_STATE() as varchar)
print 'Error Line : ' + cast(ERROR_LINE() as varchar)
print 'Error Message : ' + cast(ERROR_MESSAGE() as varchar)
end catch
Output will be...
Error No : 8134
Error Severity : 16
Error State : 1
Error Line : 2
Error Message : Divide by zero error encounter
begin try
print 1/0
print 'Sucessfully executed try block.'
end try
begin catch
print 'Error No : ' + cast(ERROR_NUMBER() as varchar)
print 'Error Severity : ' + cast(ERROR_SEVERITY() as varchar)
print 'Error State : ' + cast(ERROR_STATE() as varchar)
print 'Error Line : ' + cast(ERROR_LINE() as varchar)
print 'Error Message : ' + cast(ERROR_MESSAGE() as varchar)
end catch
Output will be...
Error No : 8134
Error Severity : 16
Error State : 1
Error Line : 2
Error Message : Divide by zero error encounter
Swapping Numeric Value Script in T-SQL
Here this script swapping the value of @a and @b variable.
declare @a int
declare @b int
declare @c int
set @a = 123
set @b = 543
print 'Before swapping a : ' + cast(@a as varchar)
print 'Before swapping b : ' + cast(@b as varchar)
set @a = @a + @b
set @b = @a - @b
set @a = @a - @b
print 'After swapping a : ' + cast(@a as varchar)
print 'After swapping b : ' + cast(@b as varchar)
Output will be...
Before swapping a : 123
Before swapping b : 543
After swapping a : 543
After swapping b : 123
Amount Into Word Script in T-SQL
Here this script display amount in word of @acceptedAmount = 111111111.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CalRupees]
(
@rupee int
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @result varchar(100)
-- Add the T-SQL statements to compute the return value here
--SET @result = 'Anil'
if(@rupee >= 1 AND @rupee <= 10)
begin
if((@rupee % 10) = 1) set @result = 'One'
if((@rupee % 10) = 2) set @result = 'Two'
if((@rupee % 10) = 3) set @result = 'Three'
if((@rupee % 10) = 4) set @result = 'Four'
if((@rupee % 10) = 5) set @result = 'Five'
if((@rupee % 10) = 6) set @result = 'Six'
if((@rupee % 10) = 7) set @result = 'Seven'
if((@rupee % 10) = 8) set @result = 'Eight'
if((@rupee % 10) = 9) set @result = 'Nine'
if((@rupee % 10) = 0) set @result = 'Ten'
--set @result = cast(@rupee as varchar)
END
if(@rupee > 9 AND @rupee < 20)
begin
if(@rupee = 11) set @result = 'Eleven'
if(@rupee = 12) set @result = 'Twelve'
if(@rupee = 13) set @result = 'Thirteen'
if(@rupee = 14) set @result = 'Forteen'
if(@rupee = 15) set @result = 'Fifteen'
if(@rupee = 16) set @result = 'Sixteen'
if(@rupee = 17) set @result = 'Seventeen'
if(@rupee = 18) set @result = 'Eighteen'
if(@rupee = 19) set @result = 'Nineteen'
end
if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) = 0) set @result = 'Twenty'
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) = 0) set @result = 'Thirty'
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) = 0) set @result = 'Forty'
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) = 0) set @result = 'Fifty'
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) = 0) set @result = 'Sixty'
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) = 0) set @result = 'Seventy'
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) = 0) set @result = 'Eighty'
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) = 0) set @result = 'Ninty'
if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Twenty One'
if((@rupee % 10) = 2) set @result = 'Twenty Two'
if((@rupee % 10) = 3) set @result = 'Twenty Three'
if((@rupee % 10) = 4) set @result = 'Twenty Four'
if((@rupee % 10) = 5) set @result = 'Twenty Five'
if((@rupee % 10) = 6) set @result = 'Twenty Six'
if((@rupee % 10) = 7) set @result = 'Twenty Seven'
if((@rupee % 10) = 8) set @result = 'Twenty Eight'
if((@rupee % 10) = 9) set @result = 'Twenty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Thirty One'
if((@rupee % 10) = 2) set @result = 'Thirty Two'
if((@rupee % 10) = 3) set @result = 'Thirty Three'
if((@rupee % 10) = 4) set @result = 'Thirty Four'
if((@rupee % 10) = 5) set @result = 'Thirty Five'
if((@rupee % 10) = 6) set @result = 'Thirty Six'
if((@rupee % 10) = 7) set @result = 'Thirty Seven'
if((@rupee % 10) = 8) set @result = 'Thirty Eight'
if((@rupee % 10) = 9) set @result = 'Thirty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Forty One'
if((@rupee % 10) = 2) set @result = 'Forty Two'
if((@rupee % 10) = 3) set @result = 'Forty Three'
if((@rupee % 10) = 4) set @result = 'Forty Four'
if((@rupee % 10) = 5) set @result = 'Forty Five'
if((@rupee % 10) = 6) set @result = 'Forty Six'
if((@rupee % 10) = 7) set @result = 'Forty Seven'
if((@rupee % 10) = 8) set @result = 'Forty Eight'
if((@rupee % 10) = 9) set @result = 'Forty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Fifty One'
if((@rupee % 10) = 2) set @result = 'Fifty Two'
if((@rupee % 10) = 3) set @result = 'Fifty Three'
if((@rupee % 10) = 4) set @result = 'Fifty Four'
if((@rupee % 10) = 5) set @result = 'Fifty Five'
if((@rupee % 10) = 6) set @result = 'Fifty Six'
if((@rupee % 10) = 7) set @result = 'Fifty Seven'
if((@rupee % 10) = 8) set @result = 'Fifty Eight'
if((@rupee % 10) = 9) set @result = 'Fifty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Sixty One'
if((@rupee % 10) = 2) set @result = 'Sixty Two'
if((@rupee % 10) = 3) set @result = 'Sixty Three'
if((@rupee % 10) = 4) set @result = 'Sixty Four'
if((@rupee % 10) = 5) set @result = 'Sixty Five'
if((@rupee % 10) = 6) set @result = 'Sixty Six'
if((@rupee % 10) = 7) set @result = 'Sixty Seven'
if((@rupee % 10) = 8) set @result = 'Sixty Eight'
if((@rupee % 10) = 9) set @result = 'Sixty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Seventy One'
if((@rupee % 10) = 2) set @result = 'Seventy Two'
if((@rupee % 10) = 3) set @result = 'Seventy Three'
if((@rupee % 10) = 4) set @result = 'Seventy Four'
if((@rupee % 10) = 5) set @result = 'Seventy Five'
if((@rupee % 10) = 6) set @result = 'Seventy Six'
if((@rupee % 10) = 7) set @result = 'Seventy Seven'
if((@rupee % 10) = 8) set @result = 'Seventy Eight'
if((@rupee % 10) = 9) set @result = 'Seventy Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Eighty One'
if((@rupee % 10) = 2) set @result = 'Eighty Two'
if((@rupee % 10) = 3) set @result = 'Eighty Three'
if((@rupee % 10) = 4) set @result = 'Eighty Four'
if((@rupee % 10) = 5) set @result = 'Eighty Five'
if((@rupee % 10) = 6) set @result = 'Eighty Six'
if((@rupee % 10) = 7) set @result = 'Eighty Seven'
if((@rupee % 10) = 8) set @result = 'Eighty Eight'
if((@rupee % 10) = 9) set @result = 'Eighty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Ninty One'
if((@rupee % 10) = 2) set @result = 'Ninty Two'
if((@rupee % 10) = 3) set @result = 'Ninty Three'
if((@rupee % 10) = 4) set @result = 'Ninty Four'
if((@rupee % 10) = 5) set @result = 'Ninty Five'
if((@rupee % 10) = 6) set @result = 'Ninty Six'
if((@rupee % 10) = 7) set @result = 'Ninty Seven'
if((@rupee % 10) = 8) set @result = 'Ninty Eight'
if((@rupee % 10) = 9) set @result = 'Ninty Nine'
end
RETURN @result
END
GO
-------------------------------------------------------------------------------------------------
declare @acceptedAmount bigint
declare @rem bigint
declare @result varchar(500)
set @result =''
set @acceptedAmount = 111111111
print ' Amount : '+ cast(@acceptedAmount as varchar)
if((@acceptedAmount / 10000000) > 0) --print 'Caror'
begin
set @rem = @acceptedAmount / 10000000
set @acceptedAmount = @acceptedAmount % 10000000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Caror'
end
if((@acceptedAmount / 100000) > 0) --print 'Lack'
begin
set @rem = @acceptedAmount / 100000
set @acceptedAmount = @acceptedAmount % 100000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Lack'
end
if((@acceptedAmount / 1000) > 0) --print 'Thousand'
begin
set @rem = @acceptedAmount / 1000
set @acceptedAmount = @acceptedAmount % 1000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Thousand'
end
if((@acceptedAmount / 100) > 0) --print 'Hundred'
begin
set @rem = @acceptedAmount / 100
set @acceptedAmount = @acceptedAmount % 100
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Hundred'
end
if((@acceptedAmount % 10) > 0) --print 'Ten'
begin
set @rem = @acceptedAmount % 100
--print @amount
set @result = @result + ' ' + [dbo].[CalRupees](@rem)
end
set @result = @result + ' ' + ' Rs.'
print ' Amount in word :'
print @result
-------------------------------------------------------------------------------------------------
drop function [dbo].[CalRupees]
-------------------------------------------------------------------------------------------------
Output will be...
Amount : 111111111
Amount in word :
Eleven Caror Eleven Lack Eleven Thousand One Hundred Eleven Rs.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CalRupees]
(
@rupee int
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @result varchar(100)
-- Add the T-SQL statements to compute the return value here
--SET @result = 'Anil'
if(@rupee >= 1 AND @rupee <= 10)
begin
if((@rupee % 10) = 1) set @result = 'One'
if((@rupee % 10) = 2) set @result = 'Two'
if((@rupee % 10) = 3) set @result = 'Three'
if((@rupee % 10) = 4) set @result = 'Four'
if((@rupee % 10) = 5) set @result = 'Five'
if((@rupee % 10) = 6) set @result = 'Six'
if((@rupee % 10) = 7) set @result = 'Seven'
if((@rupee % 10) = 8) set @result = 'Eight'
if((@rupee % 10) = 9) set @result = 'Nine'
if((@rupee % 10) = 0) set @result = 'Ten'
--set @result = cast(@rupee as varchar)
END
if(@rupee > 9 AND @rupee < 20)
begin
if(@rupee = 11) set @result = 'Eleven'
if(@rupee = 12) set @result = 'Twelve'
if(@rupee = 13) set @result = 'Thirteen'
if(@rupee = 14) set @result = 'Forteen'
if(@rupee = 15) set @result = 'Fifteen'
if(@rupee = 16) set @result = 'Sixteen'
if(@rupee = 17) set @result = 'Seventeen'
if(@rupee = 18) set @result = 'Eighteen'
if(@rupee = 19) set @result = 'Nineteen'
end
if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) = 0) set @result = 'Twenty'
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) = 0) set @result = 'Thirty'
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) = 0) set @result = 'Forty'
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) = 0) set @result = 'Fifty'
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) = 0) set @result = 'Sixty'
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) = 0) set @result = 'Seventy'
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) = 0) set @result = 'Eighty'
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) = 0) set @result = 'Ninty'
if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Twenty One'
if((@rupee % 10) = 2) set @result = 'Twenty Two'
if((@rupee % 10) = 3) set @result = 'Twenty Three'
if((@rupee % 10) = 4) set @result = 'Twenty Four'
if((@rupee % 10) = 5) set @result = 'Twenty Five'
if((@rupee % 10) = 6) set @result = 'Twenty Six'
if((@rupee % 10) = 7) set @result = 'Twenty Seven'
if((@rupee % 10) = 8) set @result = 'Twenty Eight'
if((@rupee % 10) = 9) set @result = 'Twenty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Thirty One'
if((@rupee % 10) = 2) set @result = 'Thirty Two'
if((@rupee % 10) = 3) set @result = 'Thirty Three'
if((@rupee % 10) = 4) set @result = 'Thirty Four'
if((@rupee % 10) = 5) set @result = 'Thirty Five'
if((@rupee % 10) = 6) set @result = 'Thirty Six'
if((@rupee % 10) = 7) set @result = 'Thirty Seven'
if((@rupee % 10) = 8) set @result = 'Thirty Eight'
if((@rupee % 10) = 9) set @result = 'Thirty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Forty One'
if((@rupee % 10) = 2) set @result = 'Forty Two'
if((@rupee % 10) = 3) set @result = 'Forty Three'
if((@rupee % 10) = 4) set @result = 'Forty Four'
if((@rupee % 10) = 5) set @result = 'Forty Five'
if((@rupee % 10) = 6) set @result = 'Forty Six'
if((@rupee % 10) = 7) set @result = 'Forty Seven'
if((@rupee % 10) = 8) set @result = 'Forty Eight'
if((@rupee % 10) = 9) set @result = 'Forty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Fifty One'
if((@rupee % 10) = 2) set @result = 'Fifty Two'
if((@rupee % 10) = 3) set @result = 'Fifty Three'
if((@rupee % 10) = 4) set @result = 'Fifty Four'
if((@rupee % 10) = 5) set @result = 'Fifty Five'
if((@rupee % 10) = 6) set @result = 'Fifty Six'
if((@rupee % 10) = 7) set @result = 'Fifty Seven'
if((@rupee % 10) = 8) set @result = 'Fifty Eight'
if((@rupee % 10) = 9) set @result = 'Fifty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Sixty One'
if((@rupee % 10) = 2) set @result = 'Sixty Two'
if((@rupee % 10) = 3) set @result = 'Sixty Three'
if((@rupee % 10) = 4) set @result = 'Sixty Four'
if((@rupee % 10) = 5) set @result = 'Sixty Five'
if((@rupee % 10) = 6) set @result = 'Sixty Six'
if((@rupee % 10) = 7) set @result = 'Sixty Seven'
if((@rupee % 10) = 8) set @result = 'Sixty Eight'
if((@rupee % 10) = 9) set @result = 'Sixty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Seventy One'
if((@rupee % 10) = 2) set @result = 'Seventy Two'
if((@rupee % 10) = 3) set @result = 'Seventy Three'
if((@rupee % 10) = 4) set @result = 'Seventy Four'
if((@rupee % 10) = 5) set @result = 'Seventy Five'
if((@rupee % 10) = 6) set @result = 'Seventy Six'
if((@rupee % 10) = 7) set @result = 'Seventy Seven'
if((@rupee % 10) = 8) set @result = 'Seventy Eight'
if((@rupee % 10) = 9) set @result = 'Seventy Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Eighty One'
if((@rupee % 10) = 2) set @result = 'Eighty Two'
if((@rupee % 10) = 3) set @result = 'Eighty Three'
if((@rupee % 10) = 4) set @result = 'Eighty Four'
if((@rupee % 10) = 5) set @result = 'Eighty Five'
if((@rupee % 10) = 6) set @result = 'Eighty Six'
if((@rupee % 10) = 7) set @result = 'Eighty Seven'
if((@rupee % 10) = 8) set @result = 'Eighty Eight'
if((@rupee % 10) = 9) set @result = 'Eighty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Ninty One'
if((@rupee % 10) = 2) set @result = 'Ninty Two'
if((@rupee % 10) = 3) set @result = 'Ninty Three'
if((@rupee % 10) = 4) set @result = 'Ninty Four'
if((@rupee % 10) = 5) set @result = 'Ninty Five'
if((@rupee % 10) = 6) set @result = 'Ninty Six'
if((@rupee % 10) = 7) set @result = 'Ninty Seven'
if((@rupee % 10) = 8) set @result = 'Ninty Eight'
if((@rupee % 10) = 9) set @result = 'Ninty Nine'
end
RETURN @result
END
GO
-------------------------------------------------------------------------------------------------
declare @acceptedAmount bigint
declare @rem bigint
declare @result varchar(500)
set @result =''
set @acceptedAmount = 111111111
print ' Amount : '+ cast(@acceptedAmount as varchar)
if((@acceptedAmount / 10000000) > 0) --print 'Caror'
begin
set @rem = @acceptedAmount / 10000000
set @acceptedAmount = @acceptedAmount % 10000000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Caror'
end
if((@acceptedAmount / 100000) > 0) --print 'Lack'
begin
set @rem = @acceptedAmount / 100000
set @acceptedAmount = @acceptedAmount % 100000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Lack'
end
if((@acceptedAmount / 1000) > 0) --print 'Thousand'
begin
set @rem = @acceptedAmount / 1000
set @acceptedAmount = @acceptedAmount % 1000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Thousand'
end
if((@acceptedAmount / 100) > 0) --print 'Hundred'
begin
set @rem = @acceptedAmount / 100
set @acceptedAmount = @acceptedAmount % 100
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Hundred'
end
if((@acceptedAmount % 10) > 0) --print 'Ten'
begin
set @rem = @acceptedAmount % 100
--print @amount
set @result = @result + ' ' + [dbo].[CalRupees](@rem)
end
set @result = @result + ' ' + ' Rs.'
print ' Amount in word :'
print @result
-------------------------------------------------------------------------------------------------
drop function [dbo].[CalRupees]
-------------------------------------------------------------------------------------------------
Output will be...
Amount : 111111111
Amount in word :
Eleven Caror Eleven Lack Eleven Thousand One Hundred Eleven Rs.
Date Into Word Script in T-SQL
Here this script display date into word of @acceptedDate = getdate().
declare @acceptedDate datetime
declare @result varchar(30)
declare @day int
declare @month int
declare @year int
set @acceptedDate = getdate()
declare @tempDate varchar(20)
set @tempDate = convert(varchar,@acceptedDate,101)
print 'Input Date :' + cast(@tempDate as varchar)
set @month = cast(substring(@tempDate,1,2) as int)
set @day = cast(substring(@tempDate,4,2) as int)
set @year = cast(substring(@tempDate,7,4) as int)
if((@day % 10) = 1) set @result = cast(@day as varchar) + 'st'
if((@day % 10) = 2) set @result = cast(@day as varchar) + 'nd'
if((@day % 10) = 3) set @result = cast(@day as varchar) + 'rd'
if((@day % 10) > 3) set @result = cast(@day as varchar) + 'th'
if(@month = 1 ) set @result = @result + ' ' + 'Jan'
if(@month = 2 ) set @result = @result + ' ' + 'Feb'
if(@month = 3 ) set @result = @result + ' ' + 'Mar'
if(@month = 4 ) set @result = @result + ' ' + 'Apr'
if(@month = 5 ) set @result = @result + ' ' + 'May'
if(@month = 6 ) set @result = @result + ' ' + 'Jun'
if(@month = 7 ) set @result = @result + ' ' + 'Jul'
if(@month = 8) set @result = @result + ' ' + 'Aug'
if(@month = 9 ) set @result = @result + ' ' + 'Sep'
if(@month = 10 ) set @result = @result + ' ' + 'Oct'
if(@month = 11 ) set @result = @result + ' ' + 'Nov'
if(@month = 12 ) set @result = @result + ' ' + 'Dec'
set @result = @result + ' ' + cast(@year as varchar)
print 'Date into word :' + DATENAME(dw,@acceptedDate) + ',' + @result
Output will be...
Input Date :04/14/2008
Date into word :Monday,14th Apr 2008
declare @acceptedDate datetime
declare @result varchar(30)
declare @day int
declare @month int
declare @year int
set @acceptedDate = getdate()
declare @tempDate varchar(20)
set @tempDate = convert(varchar,@acceptedDate,101)
print 'Input Date :' + cast(@tempDate as varchar)
set @month = cast(substring(@tempDate,1,2) as int)
set @day = cast(substring(@tempDate,4,2) as int)
set @year = cast(substring(@tempDate,7,4) as int)
if((@day % 10) = 1) set @result = cast(@day as varchar) + 'st'
if((@day % 10) = 2) set @result = cast(@day as varchar) + 'nd'
if((@day % 10) = 3) set @result = cast(@day as varchar) + 'rd'
if((@day % 10) > 3) set @result = cast(@day as varchar) + 'th'
if(@month = 1 ) set @result = @result + ' ' + 'Jan'
if(@month = 2 ) set @result = @result + ' ' + 'Feb'
if(@month = 3 ) set @result = @result + ' ' + 'Mar'
if(@month = 4 ) set @result = @result + ' ' + 'Apr'
if(@month = 5 ) set @result = @result + ' ' + 'May'
if(@month = 6 ) set @result = @result + ' ' + 'Jun'
if(@month = 7 ) set @result = @result + ' ' + 'Jul'
if(@month = 8) set @result = @result + ' ' + 'Aug'
if(@month = 9 ) set @result = @result + ' ' + 'Sep'
if(@month = 10 ) set @result = @result + ' ' + 'Oct'
if(@month = 11 ) set @result = @result + ' ' + 'Nov'
if(@month = 12 ) set @result = @result + ' ' + 'Dec'
set @result = @result + ' ' + cast(@year as varchar)
print 'Date into word :' + DATENAME(dw,@acceptedDate) + ',' + @result
Output will be...
Input Date :04/14/2008
Date into word :Monday,14th Apr 2008
Sum Of Digits Script in T-SQL
Here this script display sum of digits of @acceptedNo = 123.
declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result + @rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Sum of digits of Input no : ' + cast(@result as varchar)
Output will be...
Input no :123
Sum of digits of Input no : 6
declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result + @rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Sum of digits of Input no : ' + cast(@result as varchar)
Output will be...
Input no :123
Sum of digits of Input no : 6
Reverse No Script in T-SQL
Here this script display reverse no of @acceptedNo = 123.
declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result * 10) + (@rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Reverse no of Input no : ' + cast(@result as varchar)
Output will be...
Input no :123
Reverse no of Input no : 321
declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result * 10) + (@rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Reverse no of Input no : ' + cast(@result as varchar)
Output will be...
Input no :123
Reverse no of Input no : 321
Subscribe to:
Posts (Atom)