Functions for Manipulating Data in SQL Server

In SQL Server, there are numerous built-in functions that help you manipulate data effectively. Below are some key functions for working with datetime and strings.

Working with Datetime

1. GETDATE():

The GETDATE() function returns the current date and time of the server.

SELECT GETDATE() AS CurrentDateTime;

2. CONVERT(date, GETDATE()):

This function converts the datetime value returned by GETDATE() to a date type, which includes only the date part.

SELECT CONVERT(date, GETDATE()) AS CurrentDate;

3. CONVERT(time, GETDATE()):

Similarly, this function converts the datetime value returned by GETDATE() to a time type, which includes only the time part.

SELECT CONVERT(time, GETDATE()) AS CurrentTime;

4. Extracting Date and Time Parts:

SQL Server provides functions to extract specific parts of a date, such as the year, month, or day.

YEAR(): Extracts the year from a date.

SELECT YEAR(GETDATE()) AS CurrentYear;

MONTH(): Extracts the month from a date.

SELECT MONTH(GETDATE()) AS CurrentMonth;

DAY(): Extracts the day of the month from a date.

SELECT DAY(GETDATE()) AS CurrentDay;

Working with Strings

1. LEN()

The LEN() function returns the length of a string.

SELECT LEN('SQL Server') AS StringLength;

2. LOWER()

The LOWER() function converts all characters in a string to lowercase.

SELECT LOWER('SQL SERVER') AS LowercaseString;

3. UPPER()

The UPPER() function converts all characters in a string to uppercase.

SELECT UPPER('sql server') AS UppercaseString;

4. REPLACE()

The REPLACE() function replaces occurrences of a specified substring with another substring.

SELECT REPLACE('SQL Server 2024', '2024', '2019') AS ReplacedString;

5. SUBSTRING()

The SUBSTRING() function extracts a portion of a string, starting from a specified position and for a specified length.

SELECT SUBSTRING('SQL Server 2024', 5, 6) AS SubString;

6. CONCAT()

The CONCAT() function concatenates two or more strings together.

SELECT CONCAT('SQL ', 'Server ', '2024') AS ConcatenatedString;

7. STRING_SPLIT()

The STRING_SPLIT() function splits a string into a table of substrings based on a specified delimiter.

SELECT value AS SplittedString
FROM STRING_SPLIT('SQL,Server,2024', ',');