SQL Server - Working with Dates Part 1

Ian & I have received a lot of queries from past delegates about handling various date challenges when extracting data from SQL Server and Oracle databases, so we are adding them to our blog for your reference. There will be a series of posts and this is Part 1.

The following examples are for SQL Server, and are based on the AdventureWorks2014 sample database.

The following queries uses the DATENAME and DATEADD functions.

DATENAME Function

DATENAME can be used to extract a specified part of a date/time value. It takes two arguments:

• The first argument is a keyword or abbreviation that represents the part to be extracted, such as DAY, WEEKDAY, MONTH, YEAR, HOUR
• The second argument is the actual date or datetime value to extract it from.

The following line from the query extracts the day of the week as a word from the OrderDate value.

DATENAME(WEEKDAY, OrderDate) AS 'Weekday of Order Date',

DATEADD Function

The DATEADD function can be used to add a quantity of a specified unit to a date or datetime value. It takes three arguments.

• The first argument is the unit to work in, such as DAY, MONTH, YEAR, HOUR, MINUTE.
• The second argument is the quantity to add (make this negative to subtract)
• The third argument is the date or datetime value to add to (or subtract from)

The following example adds 30 days to the OrderDate:

DATEADD(DAY, -30, OrderDate) AS '30 Days from Order Date',

The following example subtracts 30 days from the OrderDate:

DATEADD(DAY, 30, OrderDate) AS '30 Days from Order Date',

The following screen shot shows the results of the DATENAME and DATEDIFF examples:

So armed with these functions here we go!

Question 1: How do I retrieve the date of the first and last day of a date's month?

/*
	Display the day of the week for the 1st and last day of the month of a given date 
*/


SELECT CONVERT(VARCHAR(10),OrderDate,103) AS 'Order Date', 
	DATENAME(WEEKDAY, OrderDate) AS 'Weekday of Order Date',
	DATEADD(DAY, 30, OrderDate) AS '30 Days from Order Date',
	DATEADD(DAY, -30, OrderDate) AS '30 Days from Order Date',
	CONVERT(VARCHAR(10),DATEADD(DAY,-DAY(OrderDate)+1,OrderDate),103) 
			AS '1st Of Order''s Month',
	CONVERT(VARCHAR(10),DATENAME(WEEKDAY, DATEADD(DAY,-DAY(OrderDate)+1,OrderDate)),103) 
			AS 'Weekday Of 1st Of Order''s Month',
	CONVERT(VARCHAR(10),DATEADD(DAY, -DAY(DATEADD(MONTH, 1, OrderDate)) ,DATEADD(MONTH, 1, OrderDate)),103) 
			AS 'Last Day Of Order''s Month',
	DATENAME(WEEKDAY, DATEADD(DAY, -DAY(DATEADD(MONTH, 1, OrderDate)),DATEADD(MONTH, 1, OrderDate))) 
			AS 'Last Weekday Of Order''s Month'
FROM Sales.SalesOrderHeader

By  nesting these functions we are able to derive the first and last days of the month for an order date. The screen shot shows the results:

The expression deriving the “1^st of Order’s Month” uses the DATEADD function which subtracts the correct number of days from the OrderDate to get to the 1^st of the month (day of the month of the orderdate plus 1.

	CONVERT(VARCHAR(10),DATEADD(DAY,-DAY(OrderDate)+1,OrderDate),103) 
			AS '1st Of Order''s Month',

The expression deriving the “Last Day Of Order’s Month” uses two instances of the DATEADD function, one nested inside the other. 

	CONVERT(VARCHAR(10),DATEADD(DAY, -DAY(DATEADD(MONTH, 1, OrderDate)) ,DATEADD(MONTH, 1, OrderDate)),103) 
			AS 'Last Day Of Order''s Month',

The innermost DATEADD (the one on the right) adds 1 month to the orderdate.

        DATEADD(MONTH, 1, OrderDate)

The outermost DATEADD (the one on the left) uses the innermost as its third argument (the date to add days to or subtract from) and subtracts the appropriate number of days from this date.

        DATEADD(DAY, -DAY(DATEADD(MONTH, 1, OrderDate)) ,DATEADD(MONTH, 1, OrderDate))

For example, suppose the orderdate was 25/04/2016:

• Adding on month to this would give us 25/05/2016.
• -DAY(DATEADD(MONTH, 1, OrderDate)) returns -25
• 25 days are subtracted from 25/05/2016 to give us a date of 30/04/2016.

Suppose the date was 30/01/2016:

• Adding one month to this would give us 29/02/2016
• -DAY(DATEADD(MONTH, 1, OrderDate)) returns -29
• 29 days are subtracted from 29/02/2016 to give us a date of 31/01/2016

It is important to use the date a month on inside the DAY function as you will end up with incorrect dates if the day value of the orderdate month does not exisit in the next month (e.g. 31^st January – there is no 31^st February – this would result in only 28 or  29 days being subtracted instead of 31).

If you would like to learn more about SQL Server SQL querying take a look at our range of SQL Server Training courses: http://ptr.co.uk/databases-business-intelligence-courses