SQL Server Built-in Functions -2

Published in

DevOps.dev

5 min readFeb 10, 2024

In the last blog post, I started to write about commonly used Sql Server pre-defined functions and Part — 1 is related with string functions. You can fins the previous function lists at the bottom of each post page.

Here is the items I will talk about function categories;

String Functions
Date & Time Functions
Conversion Functions
Mathematical Functions
Aggregate Functions

I already post about string functions and will continue Date & Time Functions in this post.

T-SQL derives all system data and time functions from the computer’s operating system that Sql Server is installed on.

SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME

sysdatetime returns the datetime2(7) value containing date and time and without time zone offset.
sysdatetimeoffset returns the datetimeoffset(7) value containing date and time with time zone offset
sysutcdatetime returns the datetime2(7) value containing date and time as UTC time.

select SYSDATETIME() as SysDateTime, SYSDATETIMEOFFSET() as SysDateTimeOffset,SYSUTCDATETIME() as SysUTCDatetime

CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE()

all of these functions returns the datetime value containing date and time without time zone offset, but getutcdate returns with UTC time.

select CURRENT_TIMESTAMP as "Current_TimeStamp", SYSDATETIME() as SysDateTime, GETUTCDATE() as GetUTCDate

DATENAME

DATENAME function returns the specified datepart of the specified date and returns string value.

--Syntax 
SELECT DATENAME(datepart, date)

--Example
SELECT DATENAME(year, GETDATE())  
      ,DATENAME(month, GETDATE())  
      ,DATENAME(day, GETDATE())  
      ,DATENAME(dayofyear, GETDATE())  
      ,DATENAME(weekday, GETDATE());

DAY, MONTH, YEAR

These functions can be used to get day, month or year from a specified date and returns integer value.

--Syntax
SELECT YEAR(date), MONTH(date), DAY(date)

--Example
SELECT YEAR(GETDATE()) AS CurrentYear,
       MONTH(GETDATE()) AS CurrentMonth,
       DAY(GETDATE()) AS CurrentDay;

DATEPART

This function can be used to get specified datepart of a specified date and returns integer value.

-- Syntax 
SELECT DATEPART(datepart, date)

-- Example
SELECT DATEPART(YEAR, GETDATE()) AS CurrentYear,
       DATEPART(QUARTER, GETDATE()) AS CurrentQuarter,
       DATEPART(MONTH, GETDATE()) AS CurrentMonth,
       DATEPART(DAYOFYEAR, GETDATE()) AS DayOfYear,
       DATEPART(DAY, GETDATE()) AS CurrentDay,
       DATEPART(weekday, GETDATE()) AS WeekDay;

DATEFROMPARTS

This functions can be used to build a date from specified day, month and years.

-- Syntax 
SELECT DATEFROMPARTS(year, month, day)

-- Example
SELECT DATEFROMPARTS(2024, 2, 9) AS CustomDate;

DATEDIFF

This function can be used to get date difference between two specified date with specified datepart.

-- Syntax
SELECT DATEDIFF(datapart, startingdate,endingdate)

-- Example
SELECT '2024-01-01' AS StartingDate, 
       '2024-01-23' AS EndingDate, 
       DATEDIFF(DAY, '2024-01-01', '2024-01-23') AS DayDifference, 
       DATEDIFF(WEEK, '2024-01-01', '2024-02-10') AS WeekDifference,
       DATEDIFF(MONTH, '2023-06-01', '2024-01-23') AS MonthDifference,
       DATEDIFF(YEAR, '2013-01-01', '2024-01-23') AS YearDifference;

DATEADD

This functions can be used to get a new datetime by adding an interval to the specified datepart of the specified date. This adding interval value can be positive or negative value.

-- Syntax
SELECT DATEADD(datepart,interval,date)
-- Example
SELECT DATEADD(DAY, 3, '2024-02-09') AS ThreeDaysLater,
       DATEADD(DAY, -3, '2024-02-09') AS ThreeDaysBefore,
       DATEADD(MONTH, 3, '2024-02-09') AS ThreeMonthsLater,
       DATEADD(MONTH, -3, '2024-02-09') AS ThreeMonthsBefore,
       DATEADD(YEAR, 3, '2024-02-09') AS ThreeYearsLater,
       DATEADD(YEAR, -3, '2024-02-09') AS ThreeYearsBefore;

EOMONTH

This function returns the last day of the month of specified date.

-- Syntax 
SELECT EOMONTH(specifieddate)
-- Example 
SELECT EOMONTH(GETDATE()) AS LastDayOfMonth;

ISDATE

This functions can be used to determine given value is date or not. Parameter can be in date or datetime type. Result will be 0 (valid date) or 1 (not valid date). If the given date type is datetime, the range of the given date should be between 1753–01–01 and 9999–12–31 for being valid. If the given date type is date, the of the given date should be between 0001–01–01 and 9999–12–31.

-- Syntax 
SELECT ISDATE(date)
-- Example
SELECT ISDATE('2024-02-09') AS ValidIsDate,
       ISDATE('2024-02-30') AS NotValidIsDate,
       ISDATE('2024-02-09 14:00:00') AS ValidIsDatetime,
       ISDATE('20024-02-09 14:00:00') AS NotValidIsDatetime;

Until this part, all of the functions are directly related with date and times but there are some other functions which are frequently used with date and time values. These functions are FORMAT, CAST and CONVERT. Let’s see what these functions are and how they are used.

FORMAT

This function can be used to get a specified date/time and number values as specified format with cultural option.

-- Syntax 
SELECT FORMAT(value, format, culture(optional))
-- Example
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS CulturalFormatUS,
       FORMAT(GETDATE(), 'd', 'en-gb') AS CulturalFormatUK, 
       FORMAT(GETDATE(), 'dd/MM/yyyy') AS CustomFormatDate,
       FORMAT(123456789, '###-###-###') AS CustomFormat,
       FORMAT(1250, 'C', 'en-us') AS CurrencyFormat,
       FORMAT(GETDATE(),N'hh:mm tt') TimeFormat;

CAST and CONVERT

Cast and Convert functions are used to convert a value from one data type to another data type.

I will give only date/time related examples because there are many different types of usage.

-- Syntax
CAST(value AS data_type(length, optional))

CONVERT(data_type(length, optional), value, style(optional))

SELECT GETDATE() UnconvertedDateTime, 
       CAST(GETDATE() as NVARCHAR(20)) CastUsage, 
       CONVERT(VARCHAR, GETDATE(), 22) AS CovertedDateInUSFormat;