DevOps.dev

Devops.dev is a community of DevOps enthusiasts sharing insight, stories, and the latest…

Follow publication

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.

Sql Server Programlama - Basics

9 stories

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Published in DevOps.dev

Devops.dev is a community of DevOps enthusiasts sharing insight, stories, and the latest development in the field.

Written by Kendime Notlar

👨‍👩‍👦‍👦Husband & father of two sons, 💻DBA, volunteer of data engineering, Founder of DataWiser

No responses yet

Write a response