Date and Time Functions in T-SQL

One of the first topics we come across when starting to learn any programming language is types and the functions associated with these types.

The same principle applies when learning T-SQL.

This topic deals with the study of functions intended for working with time and date in T-SQL. Also, in practice, you will understand how to use most of the functions mentioned above.

First, let's consider what types are available for working with time and date in T-SQL:

1. date
2. time
3. smalldatetime
4. datetime
5. datetime2
6. datetimeoffset

Before we jump into the topic, let's see what a function is.

  The main purpose of functions is to modify the given data. For example, if you were given the word "hello", you can change this word and make certain additions to it using ready-made functions.

Examples:

• "hello" - take the first three characters - "hel"
• "hello" - add the word "friend" to the end of the word - "hello friend"
• "hello" - replace the letter "e" with the letter "o" - "hollo" and so on

  The same rule applies to functions designed to work with time and date.

Due to these functions, you can convert the time and date to any time zone, take only the date or only the time part of it, and add the hour, day, year, month, etc. to that date.

T-SQL time and date functions allow you to perform dozens of operations on a given date.

Functions for working with time and date:

T-SQL has the following functions for working with time and date:

1. GetDate() – returns the current time according to the configured local time. Returns a datetime result
2. GetUtcDate() – returns the current UTC according to the configured local time. Returns a datetime result.
3. Current_timestamp - returns the current time according to the configured local time. Returns a datetime result.
4. SysDatetime() – returns the current time according to the configured local time. Returns a result of type datetime2
5. SysutcDatetime()-returns the current UTC according to the configured local time. Returns a result of type datetime2
6. Sysdatetimeoffset() - returns the current time with an offset relative to the configured local time. Returns a result of type datetimeoffset().

   SELECT GETDATE() as 'getdate',
   	GETUTCDATE() as 'getUtcDate',
   	CURRENT_TIMESTAMP as 'current_timestamp'

   SELECT SYSDATETIME() as 'sysdatetime',
   	SYSUTCDATETIME() as 'sysutcdatetime',
   	SYSDATETIMEOFFSET() as 'sysdatetimeoffset'

   Since the date is configured to UTC on my computer, there doesn't seem to be a difference between GetDATE and GETUTCDATE and SYSDATETIME and SYSUTCDATETIME. But if your computer is configured for a different date in UTC, then the date will be different. (In most cases you will only see the time difference)

   Such a question arises: If these dates all indicate the present time, what is the difference between them?

   Our first 3 functions (GETDATE(), GETUTCDATE(), and CURRENT_TIMESTAMP()) return datetime results. As we know, this type(datetime) allows rounding and is not a SQL standard type. It also takes up 8 bytes of space as a fix.

   Current_timestamp is a SQL standard function. For code portability, it is recommended to use Current_timestamp instead of GETDATE().

   Our next 2 functions (SYSDATETIME(), SYSUTCDATETIME()) return datetime2 type. Depending on the configuration, this type can occupy 6, 7, or 8 bytes of memory. Datetime2 is also a SQL-compliant language and does not allow rounding.

   For the reasons listed above, in practice, it is always recommended to use the Sysdatetime(), Sysutcdatetime(), and Sysdatetimeoffset() functions.

   As it can be seen, the issue here is based on the difference of their types rather than the difference between the functions.

   And our last function (SysDatetimeoffset())returns a result of type datetimeoffset and allows us to develop programs for universal/international use that are not dependent on the date. Because the date is stored in a universal format, not based on a specific local time.

   The functions that return the current time and date in T-SQL (the 6 functions above) take the information from the GetSystemTimeAsFileTime function of the Windows operating system.

7. DatePart – returns the date part for the specified interval. For example, if the given date is just year, month, day, etc. This function is used if we want to take parts like
   
    
8. DateName – returns the date part for the specified interval. For example, if the given date is just a year or day it will act like DatePart. Unlike DatePart, it also returns dates that match the expression. For example, if MONTH with DatePart returns 08, MONTH with DateName will return August.
   
    
9. *FROMPARTS – Several functions in T-SQL end with the postfix FROMPARTS. This includes the following dates:
   1. DATE FROM PARTS
   2. TIMEFROMPARTS
   3. DATETIMEFROMPARTS
   4. SMALLDATETIMEFROMPARTS
   5. DATETIME2FROMPARTS
   6. DATETIMEOFFSETFROMPARTS

The purpose of these functions is to manually create the types specified in the prefix.

That is, we can create information in the given types from individual numbers that we have

 

10. DateAdd – is used to add a new value to a given date as defined in an interval or subtract a value from the date defined in a given interval. This function is one of the most used functions in practice.

 

SELECT DATEADD(MONTH, 10, SYSDATETIME()) as added10Months -- add 10 months to the given date
      , DATEADD(DAY, 320, SYSDATETIME()) as added320days--add 320 days to the given date
	  , DATEADD(MONTH, -23, SYSDATETIME()) as subtracted23months--remove 23 months from the given date
	  , DATEADD(DAY, 156, SYSDATETIME()) as subtracted156days--remove 156 days from the given date

The first argument in the DateAdd() function represents the interval.

The second argument represents the number corresponding to that interval. For example, if the first argument is "MONTH" and the second argument is "34", it means "add 34 months". If we specify the number as negative in the second argument, it means that we want to subtract the exact date from the given date. The 3rd argument indicates to which date we want to add/subtract.

11) DateDiff – shows the difference between two dates on a given interval.

The first argument is an Interval, as in the DATEADD function. This function shows how much the date given in the second argument differs from the date given in the third argument

12) EOMONTH – (END OF MONTH) is one of the most common functions that we come across when calculating in most cases, especially when calculating wages or similar types of month-end calculations in payroll systems. This function shows when the month will end on a given date.

13) YEAR – used to extract only the year from the given date.

14) MONTH – used to retrieve only the month from the given date

15) DAY – is used to take only the day from the given date.

16) ToDatetimeOffset() – concatenates given timezone to the given date.

17) SwitchOffset() – Converts given date to the given timezone.

In most cases, in interview processes, I faced the question “ What is the difference between SwitchOffset() and ToDatetimeOffset() ? “ . The answer is simple.

ToDateTimeOffset just adds a given timezone next to the given date. But unlike toDatetimeOffset, SwitchOffset uses a given timezone to convert the given date to the exact timezone date.

So that what we said does not remain at the primitive level, let us use some of these functions in real practice. Especially we will focus on using GETDATE(), DATEADD(), YEAR(), MONTH(), DATEFROMPARTS(), EOMONTH(),DATEDIFF() etc.

Let's see how functions like the above-mentioned are used.

Technical task:

We have separate tables for employee data and salary calculation. (Employees and Salaries)

The Salaries table shows what salary the employee works from what time to what time. Given any date, indicate the amount of wages received by the employee on that date.

First, let's create our tables.

CREATE DATABASE EmployeeSalariesDb;
GO
USE EmployeeSalariesDb;
GO
CREATE TABLE Employees
(EmployeeId int not null primary key identity,
Name nvarchar(40) not null,
Surname nvarchar(40) not null,
FinCode varchar(7) not null,
Startdate smalldatetime not null default GETDATE(),
Enddate smalldatetime)

GO

CREATE TABLE Salaries
(SalaryId int not null primary key identity,
EmployeeId int not null FOREIGN KEY REFERENCES Employees(EmployeeId),
Startdate date not null default GETDATE(),
EndDate date ,
Salary decimal(10,2) not null)

Every time we enter an employee into the system (Employees), we automatically add a new row to the Salaries table to describe the initial salary of the employee. For that purpose, we're using a stored procedure.

PS: For making things simple, we didn't use transactions in stored procedures.

CREATE PROC usp_AddEmployee
			@Name nvarchar(40),
			@Surname nvarchar(40),
			@FinCode varchar(7),
			@Salary decimal(10,2),
			@Startdate date null
AS BEGIN
BEGIN TRAN


IF @Startdate is null
BEGIN
SELECT @Startdate = GETDATE();
END

INSERT INTO Employees(Name, Surname, FinCode,Startdate)
VALUES(@Name, @Surname, @FinCode, @Startdate)

INSERT INTO Salaries(EmployeeId, Salary,Startdate) VALUES(SCOPE_IDENTITY(), @Salary,@Startdate)
COMMIT TRAN 

END

Now let's write a procedure to give the EndDate for the old salary and add a new row in the Salaries table every time the employee's salary changes:

CREATE PROC usp_EmployeeWithNewSalary
			@EmployeeId decimal(10,2),
			@Startdate date,
			@Salary decimal(10,2)
AS BEGIN
BEGIN TRAN
DECLARE @lastId as int;

SELECT TOP 1 @lastId = SalaryId FROM Salaries
WHERE EmployeeId = @EmployeeId AND EndDate is null
ORDER BY SalaryId DESC

UPDATE Salaries
SET EndDate = DATEADD(DAY, -1, @StartDate)
WHERE SalaryId = @lastId

INSERT INTO Salaries(EmployeeId, Salary, Startdate) VALUES(@EmployeeId, @Salary, @Startdate)
COMMIT TRAN 

END

The above procedure simply finds an existing employee, sets his EndDate to one day before the given date, and adds a new Salary row to that table starting with the given start date. Now let's write the salary change for employees and one employee in the system:

EXEC usp_AddEmployee 'Adam','Cambel','1234567',8000,'2020-10-12'
EXEC usp_AddEmployee 'Max','Nohan','1233234',6000,'2022-05-17'

SELECT * FROM Employees;
SELECT * FROM Salaries ORDER BY EmployeeId;

Let's do some changes to the employee id = 10.

EXEC usp_EmployeeWithNewSalary 10,'2022-10-04',9200
EXEC usp_EmployeeWithNewSalary 10,'2023-04-17',10200

Finally, let's write our function that performs the calculation process.

Our function below finds and calculates the employee's salary according to the given date( @Salarydate ). For calculation, the "salary/number of days on a given date * number of days worked in that month" type of formula is calculated

CREATE FUNCTION [dbo].[ufn_getEmployeeSalaryForGivenDate] (@Salarydate as datetime, @employeeId as int)
RETURNS Decimal(10,2) as
BEGIN

DECLARE @salaryForDate as decimal(10,2);

WITH CTE
AS
(
SELECT CASE WHEN
YEAR(S.Startdate)=YEAR(@Salarydate) AND MONTH(S.Startdate)=MONTH(@Salarydate)
THEN S.Startdate
ELSE DATEFROMPARTS(YEAR(@Salarydate),MONTH(@Salarydate),1) END AS Startdate,
EndDate,
Salary
FROM Salaries AS S
WHERE S.Startdate < @Salarydate AND (S.EndDate >= @Salarydate OR S.EndDate IS NULL) AND S.EmployeeId = @employeeId
),
CTE2 as
(
 SELECT StartDate,Salary,
CASE WHEN
S.EndDate > EOMONTH(S.Startdate) OR S.EndDate IS NULL
THEN EOMONTH(S.Startdate)
ELSE S.EndDate END AS EndDate FROM CTE AS S
)
SELECT @salaryForDate = CEILING(Salary / DAY(EOMONTH(StartDate)) * (DATEDIFF(DAY,StartDate,EndDate)+1)) FROM CTE2;

RETURN @salaryForDate
END

Let's see the results:

Conclusion

It is very important to understand and have the ability to use date and time functions in T-SQL. Most business operations written in T-SQl require a deep understanding of these functionalities.