Using The TSQL EOMONTH Function

Here is a quick video training on how to use the T-SQL EOMONTH function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

The EOMONTH function returns the last day of a given month, or a future or past month by using the second parameter of offset. EOMONTH was introduced in SQL Server 2012, and is also available in SQL Server 2014 or newer.

Here is the sample code to go with the EOMONTH video tutorial:

-- EOMONTH (2012)
DECLARE @date DATETIME
SET @date = DATEFROMPARTS(2012, 11, 10)
SELECT EOMONTH ( @date ) AS Result;

-- next month end of month
SELECT EOMONTH ( @date, 1) AS Result;

-- last month end of month
SELECT EOMONTH ( @date, -1) AS Result;

See Also

TSQL JOIN Types Poster (Version 3)

So many times I have been asked for help with a query, where the questoin really comes down to the understanding of the difference between INNER and LEFT or RIGHT JOINs. I created this poster a few years ago and I keep it posted on the wall at the office. This way when I am trying to explain JOIN types, I just refer to the poster.

I have created the poster below to help describe JOIN types in SQL Server. This had lead to lots of confusion over time, and this is the best way that I have seen to describe them.

If you are looking for the MySQL version of the poster, click here.

Click to Download TsqlJoinTypes.pdf
TsqlJoinTypes
Click to Download TsqlJoinTypes.pdf
The diagram shows the following: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, SEMI JOIN, ANTI SEMI JOIN, LEFT OUTER JOIN with exclusion, RIGHT OUTER JOIN with exclusion, FULL OUTER JOIN, CROSS JOIN, FULL OUTER JOIN with exclusion, CROSS APPLY, OUTER APPLY, two INNER JOINs, two FULL OUTER JOINs, INNER JOIN and a LEFT OUTER JOIN and two LEFT OUTER JOINs.

If you are interested in printing out the poster, download the TsqlJoinTypes.pdf file and print it.

 

See Also:

Using The TSQL DATEADD Function

Here is a quick video training on how to use the T-SQL DATEADD function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

And here is the sample code to go with the DATEADD video tutorial:

SELECT DATEADD(month, 1, GETDATE())
SELECT DATEADD(month, -1, GETDATE())
SELECT DATEADD(month, 3, GETDATE());

See Also

Using The TSQL DATEDIFF Function

Here is a quick video training on how to use the T-SQL DATEDIFF function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

And here is the sample code to go with the DATEDIFF video tutorial:

SELECT DATEDIFF(week, '1/1/2015', GETDATE());
-- does the order matter?
SELECT DATEDIFF(week, GETDATE(), '1/1/2015');


SELECT DATEDIFF(year, '1/1/2015', GETDATE());
SELECT DATEDIFF(quarter, '1/1/2015', GETDATE());
SELECT DATEDIFF(month, '1/1/2015', GETDATE());
SELECT DATEDIFF(dayofyear, '1/1/2015', GETDATE());
SELECT DATEDIFF(day, '1/1/2015', GETDATE());
SELECT DATEDIFF(week, '1/1/2015', GETDATE());
SELECT DATEDIFF(hour, '1/1/2015', GETDATE());
SELECT DATEDIFF(minute, '1/1/2015', GETDATE());
SELECT DATEDIFF(second, '1/1/2015', GETDATE());
SELECT DATEDIFF(millisecond, '1/1/2015', GETDATE());
SELECT DATEDIFF(microsecond, '1/1/2015', GETDATE());

-- to help eliminate the overflow.
SELECT DATEDIFF(millisecond, '3/19/2015', GETDATE());
SELECT DATEDIFF(microsecond, '3/19/2015', GETDATE());

SELECT DATEDIFF(microsecond, '3/19/2015 11:00am', GETDATE());

See Also

Using The TSQL DATEPART Function

Here is a quick video training on how to use the T-SQL DATEPART function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

And here is the sample code to go with the video tutorial:

 SELECT DATEPART(year, GETDATE()) AS [year],
		DATEPART(quarter, GETDATE()) AS [quarter],
		DATEPART(month, GETDATE()) AS [month],
		DATEPART(dayofyear, GETDATE()) AS [dayofyear],
		DATEPART(day, GETDATE()) AS [day],
		DATEPART(week, GETDATE()) AS [week],
		DATEPART(weekday, GETDATE()) AS [weekday],
		DATEPART(hour, GETDATE()) AS [hour],
		DATEPART(minute, GETDATE()) AS [minute],
		DATEPART(second, GETDATE()) AS [second],
		DATEPART(millisecond, GETDATE()) AS [millisecond],
		DATEPART(microsecond, GETDATE()) AS [microsecond],
		DATEPART(nanosecond, GETDATE()) AS [nanosecond],
		DATEPART(ISO_WEEK, GETDATE()) AS [ISO_WEEK];

See Also

Using the TSQL GetDate Function

Here is a quick video tutorial on how to use the T-SQL GetDate function on SQL Server 2012, SQL Server 2014 or newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample code that goes with the video.

-- GETDATE()
SELECT GETDATE();

-- GETDATE just as a TIME
SELECT CONVERT (time, GETDATE());

-- comparing GETDATE() to others
SELECT CONVERT (time, GETDATE()) AS [getDate],
       CONVERT (time, SYSDATETIME()) AS [sysDateTime],
       CONVERT (time, SYSUTCDATETIME()) AS [sysUtcDateTime],
       CONVERT (time, CURRENT_TIMESTAMP) AS [current_timestamp],
       CONVERT (time, GETUTCDATE()) AS [getUtcDate];

See Also