How to Check SQL Server Installation Date Time ?

You need to run the following query and it will give you the date of your SQL Server Installation.

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE ‘INSTALALTIONDATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is belongs to user "NT AUTHORITYSYSTEM". This user create at the time of installation only.

Different Case Statement Scenario in SQL Server

SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END AS Comparsion_Result ---It'll Work

SELECT CASE WHEN 1=1 THEN '1' ELSE '0' END AS Comparsion_Result---It'll Work

SELECT CASE WHEN 'A'='B' THEN 'A' ELSE 'B' END AS Comparsion_Result---It'll Work

SELECT CASE WHEN 'A'='B' THEN 1 ELSE 0 END AS Comparsion_Result---It'll Work

/*Not Working*/

SELECT CASE WHEN 1=1 THEN 'A' ELSE 0 END AS Comparsion_Result---n't working

SELECT CASE WHEN 1=2 THEN 1 ELSE 'B' END AS Comparsion_Result---n't working

/*Below are the Same comparison as above these are working now*/

SELECT CASE WHEN 1=1 THEN 'A' ELSE 'B' END AS Comparsion_Result---It'll Work

SELECT CASE WHEN 1=2 THEN 'A' ELSE 'B' END AS Comparsion_Result---It'll Work

Reason: The output result is not converting into Int see the below error:

Date Formats

Date Formats

SQL Server Date Formats One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers. It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value. Standard Date Formats Date Format Standard SQL Statement Sample Output Mon DD YYYY 1 HH:MIAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1 MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98 MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998 YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01 YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01 DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72 DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972 DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05 DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005 DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98 DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998 DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1 DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1 Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1 Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1 HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53 Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default + milliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1 MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06 MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006 YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23 YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23 YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124 YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124 DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1 HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013 YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24 YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical (with milliseconds) SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489 YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250 DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1 DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function. Extended Date Formats Date Format SQL Statement Sample Output YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] 99-01-24 YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] 1999-01-24 MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 08/99 MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005 YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08 YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12 Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 20061 Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1 Month YYYY1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 20061 DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] 11 September1 Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 111 DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1 DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1 MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] 12/92 MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006 YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] 92/12 YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05 MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506 MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006 DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702 DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002 Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] Sep-02 1 Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] Sep-2002 1 DD-Mon-YY1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1 DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-20051 1 To make the month name in upper case, simply use the UPPER string function.

SQL SERVER – Difference Between GETDATE and SYSDATETIME

Sometime something so simple skips our mind. I never knew the difference between GETDATE and SYSDATETIME. I just ran simple query as following and realized the difference.

In case of GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds

SQL SERVER – CONCAT function and NULL values

CONCAT is the new T-SQL function introduced in SQL Server 2012. It is used to concatenate the values. It accepts many parameter values seperated by comma. All parameter values are concatenated to a single string.

A simple example is

SELECT CONCAT('SQL Server',' 2012')

which results to SQL Server 2012

The same can be done using + in the earlier versions

SELECT 'SQL Server'+' 2012'

which results to SQL Server 2012

But did you know the advantage of CONCAT function over +?

SELECT 'SQL Server'+' 2012'+NULL

When you execute the above, the result is NULL

But the CONCAT function will simply ignore NULL values

SELECT CONCAT('SQL Server',' 2012',NULL)

The result is SQL Server 2012

So by using CONCAT function, you do not need to worry about handling NULL values.

Table and Column Rename in SQL Server

USE AdventureWorks
GOsp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'GO

SQL ORDER BY Clause

ORDER BY clause is used to sort the data in ascending or descending order, in SQL Server by default data will not be inserted in any order even if you have an index.

Syntax:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

We will take an example of table [Person] to sort the column order by FirstName in ascending order.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName;

If you do not specify the keyword ASC, sorting by default will be in ascending order, this is equivalent of below query.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC;

If you want to order the data in descending order then you have to specify ORDER BY DESC as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName DESC;

Sorting data using the numerical position of a column name

Instead of column name you can also specify numeric position of column to sort data as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY 1 ASC;

Note

It is considered bad practice to use numerical position instead of column names. For example, if you change the positions of columns, or if you add columns then it will give error.

Using  more than one column to sort data
Here we are sorting two columns FirstName and LastName.

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName, LastName;

Combining Ascending and Descending order

With order by clause we can  combine the ASC and DESC keywords in a single statement, here is an example:

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC, LastName DESC;

Sorting Data using Column Alias

The use of an alias is allowed because the order by clause is last in logical query processing order, here is an example. [Refer SELECT Statement]

SELECT
      [FirstName]     
     ,[MiddleName]
     ,[LastName]
     ,[FirstName]+' '+[LastName] AS [FullName]
FROM [Person].[Person]
ORDER BY [FullName];

IIF() Logical Function in SQL Server

IIF() logical function has been introduced in SQL Server 2012. We already know about CASE expression and IF-Else statement in SQL Server so now you can also use IIF() function to get the same output in a short way. IIF() function’s execution plan is similar to CASE expression.

IIF() function returns one correct value based on true or false boolean expression values.

Syntax:

IIF ( boolean_expression, first_value, second_value )

IIF() function takes three arguments, first is boolean expression. When boolean expression evaluates to true then first value is the output and when boolean expression evaluates to false then second value is the output. Out of two values, highest precedence is checked and it returns the data type. We will see how to use IIF() function with examples.

Example 1:

SELECT IIF(99 > 100, 'TRUE', 'FALSE') AS OUTPUT 
GO

Here 99 is not greater than 100 so it will return second value as output which is FALSE.

Example 2:

DECLARE @STUDENT VARCHAR(100) = 'Siddiqui'
SELECT IIF(@STUDENT IN ('Manzoor', 'Siddiqui'), 'DBA', 'TechWriter') AS OUTPUT
GO

Here we are assigning and comparing string value and as assigned string value results in true so first value ‘DBA’ is the output.

Example 3:

DECLARE @gender1 VARCHAR(20) = 'M', @gender2 VARCHAR(20) = 'F', @gender3 VARCHAR(20) = 'X'
SELECT IIF(@gender1 = @gender2, 'MALE', IIF(@gender2 = @gender3, 'FEMALE', 'X')) AS Gender
GO

Here we are checking nested IIF() function to find the gender, it will return ‘X ‘as output.

Example 4:

DECLARE @valueX INT = 99, @valueY INT = 100;
SELECT IIF ( @valueX < @valueY, 'TRUE', 'FALSE') AS Output
GO

Here integer value X is less than value Y so it will return TRUE.