SQLTerminal

SQL Server – Convert Number to Month Name

The code below converts a number to a Month and displays it as a Name. It works in Multiples of 12 (ie 13 is January)

DECLARE @Month int SET @Month = 4 Select DateName( month , DateAdd( month , @Month , 0 ) - 1 )

The above code displays April: (more…)

SQL Server – Get list of databases in SQL Server Instance

Basic query to obtain a list of all databases running on a SQL Server instance.

EXEC sys.sp_databases

This runs a system stored procedure found as shown below:

SQL Server – How to split Full Name / Strings

This is simple example that can be used for any data splitting We use the data set below:

DECLARE @FullName VARCHAR(50) SELECT @FullName = Name FROM dbo.Employees WHERE EmployeeID = 11 SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS FirstName, SUBSTRING(@FullName,CHARINDEX(' ', @FullName) + 1,LEN(@FullName)) AS LastName

FirstName Explanation: The CHARINDEX is…

SQL Server – Calculate the Median of Age/Numbers

We use the AdventureWorks2012 database to demonstrate how to calculate the Median. Problem: Calculate the Median Age of all Employees(HumanResources Schema) in the database.

Solution:

SELECT AVG(b.Age) as Average FROM ( SELECT ROW_NUMBER() OVER(Order by a.Age asc) as rownumber,Age ,COUNT(*) Over() as Total FROM (SELECT BusinessEntityID,BirthDate,getdate() as TodaysDate,…

SQL Server – Calculate Age using the date of birth (DOB)

The query below obtains the age of a person based on the date of Birth.

SELECT floor(DATEDIFF(hour,DATE_OF_BIRTH,GETDATE())/8765.8127) AS Age

(more…)

SQL Server – Get Column names for a table

The code below obtains the Column details for a specific table in a specific schema

SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS a WHERE a.TABLE_NAME ='Name_of_Table' and a.TABLE_SCHEMA ='Name_of_Schema'

(more…)

SQL Server – Rename a Column name in a table

Exec sp_rename 'TableName.OldColumnName', 'NewColumnName', 'Column'

(more…)

SQL Server – Convert Date to 12 hour clock – AM or PM

The code below obtains the time portion in 12 hr clock format

SELECT Right(convert(varchar,getdate(),100),7) as PresentTime

Code explained:

SELECT convert(varchar,getdate(),100)

This obtains the date in the format defined by the style Refer post: [permalink href="56" class="linkingblogposts" Title="Date Formats SQL Server/Oracle Part 1"]Date Formats[/permalink]

The Right function above obtains…

SQLServer – Create a Date Field using Values

This article discusses how you can create a date with Individual Values for day, month and year. Date to be Created:

24th August 2010.

DECLARE @day int, @month int, @year int SET @day = 23 SET @month = 8 SET @year = 2010 SELECT dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1) as DateNeeded

Dateadd Function Syntax:

DATEADD(datepart,number,date)

For the workings of the dateadd…

SQL Server – DateAdd Function How it works

The DateAdd Function can be used to add or subtract a specified time to a Date.

Dateadd Function Syntax:

DATEADD(datepart,number,date)

Datepart

year

quarter

month

dayofyear

day

week

weekday

hour

minute

second

millisecond

microsecond

nanosecond

Abbreviation

yy, yyyy

qq, q

mm, m

dy, y

dd, d

wk, ww

dw, w

hh

mi, n

ss, s

ms

mcs

ns

Example: Add 10 days to todays date for some proposed pay to…

ASF SADFSA FAS ASDFAS DFAF ASDF

SELECT SCHEMA_NAME(t.schema_id) +'.' +t.name as 'Table Name' FROM sys.tables t join sys.columns c on c.object_id = t.object_id WHERE c.name LIKE '%SearchingColumnName%'

SQL Server - Delete Duplicate Records from a table

This article discusses one method of deleting duplicate records in a table.

Let us first setup the…

Using the AdventureWorks2012 Database, get the Sales People with the highest Sales Quota in each of the Territories.(ie Sales people with Highest Sales Quota in Territory 1 and so forth..)

Return the BusinessEntityID,First Name, Last Name,…

Using the AdventureWorks2012 Database,List Products that have yet to be ordered. Return only Products that have a ListPrice not equal to 0.00.

Return the ProductID,Product Name, ListPrice.

(more…)

Using the AdventureWorks2012 Database,show me each Product Vendor and the ‘Average Lead Time’ by each Product vendor where the ‘Average Lead Time’ is smaller than the average ‘Average Lead Time’ for all Product Vendors.

Return the ProductID,Product Name,…

Using the AdventureWorks2012 Database,calculate how many orders are for one product only. The Order Quantity of each Product is not relevant to this question.

(more…)

What is the View?

A View is a virtual table whose contents (columns and rows) are defined by a query. A view can be used to create a snapshot of information using data from different tables.

An Article on Views -an Introduction to follow…

What is a CTE?

A…