SQL Server Built-in Functions -2

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;

To get more detailed information about cast and convert please visit official Microsoft documentation here.
Hope this second function post will help you about learning path or your projects.
Feel free to contribute to make my writings get better and useful.
You can find the other post related with Sql Server Programmig Basics in the following list.

