Mastering Date and Time Manipulation in SQL Server

In the vast landscape of data analysis and database management, the adept manipulation of dates and times is an indispensable skill. Microsoft SQL Server, our trusty companion in this journey, equips us with a robust set of functions and operators for handling these temporal entities with precision and flexibility. This blog post is your guide through practical examples of date and time manipulation in SQL Server, featuring the mighty DATEADD and DATEDIFF functions.

Amalgamating Dates and Times

There comes a time when you need to unite a date with a time to birth a complete datetime value. Behold the magic of the CAST function:

SELECT

CAST(CONCAT(MyDate, ' ', CONVERT(TIME(7), TimeOnlyVarChar)) AS datetime2(7)) AS MyDateTimeType

This enchanting spell gracefully combines the date and time strings, creating a datetime2(7) entity.

Retrieving Current Date and Time

To unveil the present moment, the GETDATE() function comes to our aid:

SELECT GETDATE() AS CurrentDateTime

Proceed with caution and witness the current date and time in the format ‘2023-12-16 17:13:55.123’.

Manipulating Dates and Times with DATEADD and DATEDIFF

Enter the wizards - DATEADD and DATEDIFF. The former adds intervals to dates, the latter calculates differences. For example, conjuring the day after today:

SELECT GETDATE() + 1 AS Tomorrow

And to discern the mystical difference between today and tomorrow:

SELECT DATEDIFF(DAY, GETDATE() + 1, GETDATE()) AS Difference

Fear not, for the answer shall be 0, revealing the harmony between today and tomorrow.

Working with Specific Dates and Times

Navigate through time with precision. For instance, the code below crafts the date three months ago and the end of the previous month:

DECLARE @dateFrom datetime2

DECLARE @dateTo datetime2

SELECT @dateFrom = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

, @dateTo = DATEADD(ms,-3,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 0, 0))

SELECT @dateFrom AS Last3Months, @dateTo AS EndOfLastMonth

Marvel at the variables @dateFrom and @dateTo, capturing the essence of the past.

Converting Strings to Datetime Values

With the CONVERT function, strings gracefully transform into datetime values. Witness the metamorphosis:

DECLARE @SelectedMonth int = 1, @year int = 2010

DECLARE @date datetime = CONVERT(datetime, '28/' + REPLACE(STR(@SelectedMonth,2),' ','0') + '/' + STR(@year ,4), 103)

SELECT @date AS StringToDate

A symphony of variables orchestrates the conversion, bringing the string ‘2010-10-28’ to life as a datetime value.

Handling Multiple Dates and Times

Behold the prowess of DATEADD and DATEDIFF as they handle the ebb and flow of multiple temporal entities. For instance, capturing the beginning and end of the current month:

SELECT

DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) AS 'Beginning of the month'

, DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) AS 'End of the month'

SSRS Magic: Crafting Reports

Lastly, if you are still crafting reports in SQL Server Reporting Services (SSRS) then tet’s conjure up the magic of date manipulation in SSRS:

-- SSRS: Beginning of the Previous Month

=DateAdd("m", DateDiff("m", "1900-01-01", Today()) - 1, "1900-01-01")

Tweak the code above to manipulate into other variation and craft your reports with the finesse of a sorcerer, manipulating dates effortlessly.

As you tread the path of SQL datetime manipulation, may these examples serve as your trusty spells in crafting a symphony of precision and flexibility. Happy coding!