European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Server Full Text Search with rank values

clock September 14, 2016 20:56 by author Peter

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: Disable Tests in tSQLt

clock August 4, 2016 23:28 by author Peter

The tSQLt unit test framework that we use to do our db tests doesn't give disabled tests, this can be because each test is just a keep proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test. Here is a quick way how to prevent a test from running when the tSQLt tests run.

Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE  ProcName AS part. This code will pick the correct name and schema, so you can use the same exact code in each test.
DECLARE @IsTestDisabled bit = 1 --
set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END


So if we grab the example test from the tSQLt website. We would change it from this
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO


To  this
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS


DECLARE @IsTestDisabled bit = 1 -- set
this to 1 if you don't want the test to run
IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END

BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO

Now you will get the following in your tSQLt test run output

Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]

This will be right above this text
+----------------------+
|Test Execution Summary|
+----------------------+


I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..


To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of
DECLARE @IsTestDisabled bit = 1

You would make it
DECLARE @IsTestDisabled bit = 0

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Use sys.dm_os_performance_counters to get your Buffer cache hit ratio?

clock July 28, 2016 23:23 by author Peter

Today, I will show you How to Use sys.dm_os_performance_counters to get your Buffer cache hit ratio. In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.

Here is the query that will do that, this query will only work on SQL Server 2005 and up.
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy
Now let's look at Page life expectancy. Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Export to Excel in c# from list and using Linq filter?

clock June 22, 2016 22:03 by author Peter

Today, let me explain you about Export to Excel in c# from list and using Linq filter. You will find lot export to excel coding in other website. I'm not going to give any new in this. I am also giving here with simple List collection with basic LINQ filter and export to excel sheet.
Note: Include the Microsoft.Office.Interop.Excel in your project.

List Collection With LINQ Filter:
    Eg: lstLocal = lstFlspc.Where(m => m.ClientLocation == “Chennai”).ToList();

Export to Excel in c#.net with List and LINQ Filter Query:
    public void ExportToExcel(List<FileInfoLocal> lst)
    {
        try
        {
//Filter in List collection
          lstLocal = lstFlspc.Where(m => m.ClientPath.Path == child.Text).ToList();


          Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

          if (xlApp == null)
          {
              MessageBox.Show("Excel is not properly installed!!");
              return;
          }   

          Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
          Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;

          xlWorkBook = xlApp.Workbooks.Add(misValue);
          xlWorkSheet =(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
             

          int iRow = 1, iColumn = 1;
          //Header Test
          xlWorkSheet.Cells[iRow, iColumn] = "Column1 Name";
          xlWorkSheet.Cells[iRow, iColumn + 1] = "Column2 Name";
          xlWorkSheet.Cells[iRow, iColumn + 2] = "Column3 Name";
          xlWorkSheet.Cells[iRow, iColumn + 3] = "Column4 Name";
          xlWorkSheet.Cells[iRow, iColumn + 4] = "Column5 Name";
          iRow++;

// Filter in List collection using LINQ

          if (lstLocal!= null)
          {
          foreach (var item in lstLocal)
            {
              // .. Add other parameters here. Body Text
              xlWorkSheet.Cells[iRow, iColumn] = item.Name;
              xlWorkSheet.Cells[iRow, iColumn + 1] = item.Date;
              xlWorkSheet.Cells[iRow, iColumn + 2] = item.Path;
              xlWorkSheet.Cells[iRow, iColumn + 3] = item.UserName;
              xlWorkSheet.Cells[iRow, iColumn + 4] = item.Desc;
              iRow++;                                  
            }
          }

         string sExcelFile = Directory.GetCurrentDirectory() + "Report.xls";

         xlWorkBook.SaveAs(sExcelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
         xlWorkBook.Close(true, misValue, misValue);
         xlApp.Quit();

         releaseObject(xlWorkSheet);
         releaseObject(xlWorkBook);
         releaseObject(xlApp);

         MessageBox.Show("Excel file created successfully , you can find the file " + sExcelFile, "Export To Excell");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }



    // Release the Excel object from memory
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Remove DataBase In Sql Server Without Using Wizard?

clock June 21, 2016 22:08 by author Peter

Today, I will explain you about how to Remove DataBase In Sql Server Without Using Wizard. This system stored procedure will helps you.
exec sp_dbremove 'dataBaseName'


The following is the entire script of the stored procedure:
USE [TMS] 
GO 
/****** Object:  StoredProcedure [sys].[sp_dbremove]    Script Date: 08-12-2014 22:09:09 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER procedure [sys].[sp_dbremove] --- 1996/04/08 00:00 
    @dbname sysname = null, 
    @dropdev varchar(10) = null 
as 
    declare @dbid int 
    declare @devname sysname 
    declare @physname varchar(255) 
 
    if @dbname is null 
    begin 
        raiserror(15131,-1,-1) 
        return(1) 
    end 
 
    if lower(@dropdev) <> 'dropdev' and @dropdev is not null 
    begin 
        raiserror(15131,-1,-1) 
        return(1) 
    end 
 
    -- You must be SA to execute this sproc. 
    if (not (is_srvrolemember('sysadmin') = 1)) 
    begin 
        raiserror(15247,-1,-1) 
        return(1) 
    end 
 
    -- Make sure not trying to remove within a transaction. 
    set implicit_transactions off 
    if @@trancount > 0 
    begin 
        raiserror(15002,-1,-1,'sys.sp_dbremove') 
        return(1) 
    end 
 
    -- Check to see if database exists. 
    select @dbid = dbid from master.dbo.sysdatabases where [email protected] 
    if @dbid is null 
    begin 
        raiserror(15010,-1,-1,@dbname) 
        return(1) 
    end 
 
    -- Make sure no one is in the db. 
    if (select count(*) from master.dbo.sysprocesses where dbid = @dbid) > 0 
    begin 
        raiserror(15069,-1,-1) 
        return (1) 
    end 
 
    -- Note: database @dbname may not exist anymore 
    -- If invoke gets error, exception will abort this proc. 
    EXEC %%DatabaseRef(Name = @dbname).SetSuspect(Value = 1) 
 
    declare @dropCmd nvarchar(max) 
 
    select @dropCmd = 'drop database ' + quotename(@dbname) 
    exec (@dropCmd) 
    raiserror(15458,-1,-1) 
 
    return(0)   -- sp_dbremove 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Calculate or Find median in SQL Server?

clock June 16, 2016 21:20 by author Peter

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Calculate or Find median in SQL Server?

clock June 16, 2016 21:20 by author Peter

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server Hosting - HostForLIFE.eu :: Repair SQL Database From Suspect Mode

clock June 14, 2016 20:11 by author Anthony

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

Free ASP.NET Hosting - Europe

  • Data files or log files are corrupt.
  • Database server was shut down improperly
  • Lack of Disk Space
  • SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

  • Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

  • Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

  • Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  • For safety, take the backup of the database.
  • Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
  • There is no way to go back to the previous state of the database.
  • So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  • Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

  • Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL 2016 Hosting - HostForLIFE.eu :: How to Use Concat Function in SQL 2016?

clock May 3, 2016 00:52 by author Anthony

In this tutorial, I will explain how to use concat function in SQL 2016. CONCAT function is also known as T-SQL Function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Arguments

string_value: A string value to concatenate to the other values.

Return Types: String, the length and type of which depend on the input.

 

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.

2. It requires at least two input values; otherwise, an error is raised.

3. All arguments are implicitly converted to string types and then concatenated.

4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

5. The implicit conversion to strings follows the existing rules for data type conversions. 

6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

 

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpLastName Varchar(20) ='Carter'

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

John Carter

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John Carter

 

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

---- Set Null Value here

Declare @inpLastName Varchar(20) =NULL

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

NULL

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpContactNumber BIGINT =1234567890

 

---- Concating variables without Concat Function

Select @inpFirstName+':'+CAST(@inpContactNumber as VarcharasNameWithContact

 

NameWithContact

John :1234567890

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar))as NameWithContact

 

NameWithContact

John:1234567890

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

 

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 

 



SQL 2016 Hosting - HostForLIFE.eu :: How to Make Time Converter in SQL Server 2016?

clock April 22, 2016 00:06 by author Anthony

In this tutorial, I will explain about how to make time converter. The time converter converts the most common time units, second, hour, minute, day, week, month, year and century. Second is the SI base unit of time and officially defined as the duration of 9 192 631 770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom.

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.


Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Or, we can use the below code.


DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second


SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second


SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) '
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second


SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second


SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2012 Hosting, ASP.NET 4.5 Hosting, ASP.NET MVC 5 Hosting, SQL 2014 Hosting and SQL 2014 Hosting.

Tag cloud

Sign in