Have you ever wanted to convert a US date to UK format using SQL?

Have you ever come across the situation where you have data in US format and need it changed to UK format? Well I am going to show you how to use SQL to easily reformat the date.

I have had this problem several times in the past dealing with international clients who supply databases with the incorrect date format and use the below query to change the format.

SELECT CONVERT(VARCHAR(30), CONVERT(DATETIME, ‘6/1/2009 12:00:00 AM‘, 101), 103);

1

Find the current day, month and year in SQL Server

Have you ever wanted to return the value of the current day? Month? Or year? Well its easy in SQL and I am going to show you how.

The query for the current day is below.

SELECT DAY(GETDATE())

The query for the current month is below.

SELECT MONTH(GETDATE())

The query for the current year is below.

SELECT YEAR(GETDATE())

Each query will return a number which will correspond to the information required. For example a value of 2 returned is February in the month query.

1

Find todays date and time in SQL Server

Have you ever wondered how to get the current time and date within SQL Server? Well I am going to show you two easy methods of getting this information.

The first method is my preferred method as it’s slightly shorter than the second method.

SELECT GETDATE()

SELECT CURRENT_TIMESTAMP

1

Find a column in a database with INFORMATION_SCHEMA.COLUMNS

Have you ever just been given a column name and not known which table it was in? Needed to check whether a column existed within a database?…

Well today I was just given a column name and told the information I needed was in there but alas they didn’t know which table it was in.

I could have gone through each table but that is very inefficient and I can more effectively harness the power of SQL to get the answer quickly and easily.

I knew that the INFORMATION_SCHEMA.COLUMNS would be a great place to start and did the below query.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

1

 

The next step was to add the COLUMN_NAME in with a WHERE clause.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = ‘max_history_rows’

2

SQL JOINS – INNER,OUTER( FULL,LEFT,Right) ,CROSS, SELF

Have you ever been baffled when people talk about SQL joins? Mystified when you hear inner, outer, cross or self? Ran away in fear at the thought of joining two tables?

Well I am about to help you out and face down those fears and explain to you what joins are, the different types and what they are used for.

What is a join?

A join in SQL is a clause which combines two or more tables together within a database. The join is done on matching predicates between two or more tables and the result is a new data set.

Simply this means that it will take two tables and join them on a column within each table which has data that contains similar data.

What is an inner join?

An inner join will return only values where there is match between both corresponding columns in both tables.

Simply it means where the two columns match the rows will be returned in the new table.

What is an outer join? Left? Right? Full?

An outer join is different to an inner join as it does not require the data to match. A Left join would return all record from the left table and only the matching record from the right.

A Right join would return all record from the right table and only the matching record from the left.

A Full outer join returns all unmatched and matched data. Simply it is returning the results of a Left & Right join

What is a self-join?

A self-join is a table which is joined to itself.

What is a cross join

A cross join  is often referred to as a Cartesian product and returns all possible combinations of rows.

Powershell and my first script for Hello World.

It has become a repeated custom that whenever a person is learning a new programming language that they begin with the Hello World program. This is quite simply a rite of passage and a welcome milestone so I started here and have put the script below.


$strString = "Hello World"

write-host $strString

Hopefully in the next few weeks I will be sharing more PowerShell secrets so that everyone can learn a bit more PowerShell.

Use SQL to search a column for a range of values

Have you ever wanted to search for a column for a range of values? For example if you were searching for Product ID which ranges from 1 to 300, or look for a range of names starting A to D.

Well I can show you how to do these quickly and easily by using the power of SQL.

The below query shows there are 504 rows in the Production.Product table


USE AdventureWorks2012

GO

SELECT *

FROM [Production].[Product]

1

 

 

If you wanted to search for ProductID’s which were 1 to 4 you could do it using the below query.


USE AdventureWorks2012

GO

SELECT *

FROM [Production].[Product]

WHERE ProductID = 1

OR ProductID = 2

OR ProductID = 3

OR ProductID = 4

2

 

Whilst the above query works it is a very ugly and inefficient query as you are writing way too much code when you could use search a range which takes less time to type and is more easily read and understood.


SELECT *

FROM [Production].[Product]

WHERE ProductID BETWEEN 1 AND 4

As you can see the above query is much more readable , easily understood and more importantly is shorter which saves you time.

I would recommend using the syntax on different values and tables to see how many different  types of data you can retrieve.

 

 

 

Search for a list of values using the IN command in SQL

Have you ever been presented with a list of values which need to be found in a column or wondered how to search for a list of exact names, addresses, numbers without using wildcard searches or ranges.

Well SQL has the answer and it is the IN command.


USE AdventureWorks

GO

SELECT *

FROM HumanResources.Department

The above query produces 16 rows of information when there are no clauses filtering the results.

If you wanted to know how many departments were in the Manufacturing group you would use the below query.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Manufacturing'

If you wanted to know how many departments were in the Inventory Management group you would use the below query.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Inventory Management'

You could combine both queries as show below using the OR command to filter for more than one value.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Inventory Management'

OR GroupName ='Manufacturing'

However, though it does achieve our desired result there are is a better method which I shall show you below now using the IN command.


SELECT *

FROM HumanResources.Department

WHERE GroupName IN ('Inventory Management','Manufacturing')

The above query produces the same results but is far superior in its readability and ease of use. We can easily see the filter and there are fewer commands which measns less to go wrong and less to type.

Experiment with numbers, dates and words on the database to familiarise your self with this new query.

NULL Values – How to filter out NULLS and how to find NULLS using SQL

Have you ever wanted to find a NULL value or filter out NULL values within a column?

Well SQL has the solution and our saviour is the ‘IS NULL’ and ‘IS NOT NULL’ commands. Using them in a ‘WHERE’ clause will allow us to do the filtering out appropriately.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

The above query bring us a result of 19,614 rows contained within the AddressLine2 where no values have been filtered.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

WHERE AddressLine2 IS NULL

Using the ‘IS NULL’ clause we find that there are 19,252 rows where AddressLine2 has a Null value.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

WHERE AddressLine2 IS NOT NULL

Using the ‘IS NOT NULL’ clause we find that there are 362 rows where AddressLine2 has a value which does not contain a Null.

A quick check shows  total count of ‘IS Null’ ( 19,252) + total count of ‘IS NOT NULL’(362) equal 19,614 which shows that we have returned the correct figures and have not been tripped up by any kind of data anomalies.

AS – Use Aliases / Shorthand in SQL to make more readable queries

Have you ever wondered how I can make my SQL queries look more readable, manageable and simpler?

Well the answer is to use the ‘AS’ command as this can alias tables.

For example the below query can seem quite daunting with long table names.


USE AdventureWorks

GO

SELECT      HumanResources.Department.DepartmentID,

HumanResources.Department.Name,

HumanResources.Department.GroupName,

HumanResources.Department.ModifiedDate

FROM HumanResources.Department

However, the below query using the ‘AS’ command to alias the HumanResources.Department makes it look better, simpler and is quicker to read.

USE AdventureWorks
GO
SELECT      HRDept.DepartmentID,

HRDept.Name,

HRDept.GroupName,

HRDept.ModifiedDate

FROM HumanResources.Department AS HRDept

As you become more proficent in SQL you will find yourself aliasing tables all the time and probably won’t even notice yourself doing it