Archive

Archive for the ‘SQL Server’ Category

Convert Number to Word In SQL Sever

September 20, 2016 Leave a comment

Hi all,

Try this example to convert number to word in SQL Server


Create FUNCTION [dbo].[NumberToWords]
(
@Amount bigint
)
RETURNS nvarchar(max)
AS
BEGIN
declare @Ones table (Id int, Name nvarchar(50))
declare @Decades table (Id int, Name nvarchar(50))
insert into @Ones(Id,Name) values(0,''),(1,'One'),
(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),
(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),
(10,'Ten'),(11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),
(14,'Forteen'),(15,'Fifteen'),(16,'Sixteen'),
(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen')
insert into @Decades(Id,Name) values(20,'Twenty'),(30,'Thirty'),
(40,'Forty'),(50,'Fifty'),(60,'Sixty'),
(70,'Seventy'),(80,'Eighty'),(90,'Ninety')
declare @str nvarchar(max)
set @str=''

if(@Amount >= 1 AND @Amount <20)
set @str=@str+ (select Name from @Ones where Id=@Amount)

if(@Amount >= 20 AND @Amount <=99)
set @str=@str+ (select Name From @Decades where Id=
(@Amount- @Amount%10))+' ' +(select Name From @Ones where Id=(@Amount%10)) +' '

if(@Amount >= 100 AND @Amount <=999)
set @str=@str+ dbo.NumberToWords(@Amount/100) +'
Hundred '+dbo.NumberToWords(@Amount%100)

if(@Amount >= 1000 AND @Amount <=99999)
set @str=@str+ dbo.NumberToWords(@Amount/1000) +'
Thousand '+dbo.NumberToWords(@Amount%1000)

if(@Amount >= 100000 AND @Amount <=9999999)
set @str=@str+ dbo.NumberToWords(@Amount/100000) +'
Lac '+dbo.NumberToWords(@Amount%100000)

if(@Amount >= 10000000 )
set @str=@str+ dbo.NumberToWords(@Amount/10000000) +'
Crore '+dbo.NumberToWords(@Amount%10000000)

return @str
END

Hope this helps

Good Luck.

Categories: SQL Server

List the Name of the Months Between Date Ranges in SQL Server

August 29, 2016 Leave a comment

Hi all,

Try this example to List the Name of the Months Between Date Ranges in SQL Server


DECLARE @sDate DATETIME = '20160301', @eDate DATETIME = '20160901';

;WITH cte AS
(
SELECT @sDate AS dates
UNION ALL
SELECT DATEADD(mm,1,c.dates)
FROM cte c
WHERE c.dates < @eDate
)
SELECT DATENAME(MM, dates)
FROM cte

Hope this helps

Good Luck.

Categories: SQL Server

SQL Server Authentication enabling using Microsoft SQL Server Management Studio

Hi all,

try this scenario to Enable SQL Server Authentication using MS SQL Server Management Studio

1. First, Login to the SQL Server Management Studio using Windows Authentication. Right-click on the database instance, and go to Properties.

SQL2

2. Then on Properties page, click on Security and select SQL Server and Windows Authentication mode, and click on OK to close the Server Properties page.

SQL3

3. Now you will get dialog box saying that you should restart your SQL Server to take the changes take effect. Is is not done yet, you have to done one more thing to enable the “sa” login.

4. Now expand Security folder and go to Logins. You can see the “sa” account is disabled when you install SQL Server using Windows Authentication mode.

SQL4

5. Then right-click on the “sa” account and go to Login Properties. There you can set a password for the “sa” account.

SQL5

SQL5.1

6. Click on the Status page. There you can see the “sa” account is disabled by default. Click on the Enabled button to enable it. Then click on Ok to close the “sa” Login Properties.

SQL6

Now “sa” account is enabled and you can login to the SQL instance using the “sa” account after restarting the SQL Server.

Note: SQL Server service needs to be restarted to make this change effective.

Categories: SQL Server

Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

October 9, 2014 Leave a comment

Hi all,

Try this example to Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

In our example, we will just take one table for convenience.

1- Open MS SQL Server 2008

2- Right Click on Database >> Tasks >> Generate Scripts >>

data1

3- This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.

data2

data3

4- On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.

data4

5- The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.

data5

data6

6- On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3

data7

7- Clicking on Finish button will generate a review screen containing the required objects along with script generating data.

data8

data9

8- Clicking on Finish button one more time will generate the requested output.

data10

Hope this helps

Good Luck.

Categories: SQL Server

Search stored procedures by names

October 20, 2013 Leave a comment

Hi all,

Try this example to Search Stored Procedures by Name using this SQL Query


SELECT *
FROM sys.procedures
WHERE name LIKE '%message%'

Hope this helps

Good Luck.

Categories: SQL Server

Remove First and Last Character from string using SQL

April 4, 2013 Leave a comment

Hi all,

try this example to Remove First and Last Character from string using SQL Server

— To remove first character from string try this example :


Declare @name as varchar(30)
set @name='Yasser'
Select right(@name, len(@name)-1) as AfterRemoveFistCharacter

— To remove the last character from a string try this example :


Declare @name as varchar(30)
set @name='Yasser'
Select left(@name, len(@name)-1) as AfterRemoveLastCharacter

— To remove both (the first and last) character from a string try this example :


Declare @name as varchar(30)
set @name='Yasser'
Declare @n varchar(40)
set @n=left(@name, len(@name)-1)
Select right(@n, len(@n)-1)

Hope this helps

Good Luck.

 

Categories: SQL Server

Get Number of Days of a Month in SQL Server

March 25, 2013 Leave a comment

Hi all,

try this example to get number of days of a month using SQL Server

first i’m going to create function which will take date and will return number of days of that date


CREATE FUNCTION [dbo].[Fn_GetDaysInMonth]
( @pDate DATETIME )
RETURNS INT
AS
BEGIN

SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
 SET @pDate = @pDate - DAY(@pDate) + 1
 RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))
END

and to test this SQL Function

select [dbo].[Fn_GetDaysInMonth] (getdate()) 

Hope this helps

Good Luck.

Categories: SQL Server