Introduced in SQL Server 2008 : DateTime and DateTime2

DateTime and DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.
Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to
microseconds and avoids the 3/1000 second rounding issue.
The precision with a datetime2 is upto 100 nanoseconds.

DECLARE @dt AS DATETIME
SET @dt = GETDATE()
SELECT @dt

DECLARE @dt2 AS DATETIME2
SET @dt2 = GETDATE()
SELECT @dt2

As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds.
However the datetime2 has a larger date range, a larger default fractional precision, and optional
user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds.
The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type
only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned
earlier in case of datetime is 00:00:00 through 23:59:59.997
whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

Stuff function in T SQL

The Stuff Function in SQL Server is used to deletes a sequence of characters from a source string and then inserts a string into another string.

Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.

Starting_Position: From which index position, you want to start inserting the New_String characters.

Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function will start at Starting_Position and delete the specified number of characters from the Character_Expression

New_String: New string you want to stuff inside the Character_Expression at Starting_Position

Syntax: SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)

FROM [Source]

DECLARE @Character_Expression varchar(50)

SET @Character_Expression = 'Learn Server' 

-- Starting Position = 6 and End Position = 0

SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF' 

-- Starting Position = 7 and End Position = 6

SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF' 

-- Starting Position = 1 and End Position = 5

SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF' 

-- Starting Position = 20 and End Position = 5

SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'

One more use of STUFF to get comma separated values in a a rows as per given below example.

DECLARE @TblCity TABLE(CountryCode INT, City VARCHAR(50))

INSERT @TblCity(CountryCode, City)

VALUES

(1, 'Johannesburg'), 

(1, 'Cape Town'), --South Africa

(2, 'New York'), 

(2, 'Washington'), --USA

(3, 'Paris'),

(3, 'Nice'), --France

(4, 'Rome'), 

(4, 'Bologna'), --Itlay

(5, 'Athens'), 

(5, 'Volos') --Greece

---// Add the Following SELECT FOR XML PATH Query to concatenate the Cities belonging to the same country code onto one line:

--Concat

SELECT DISTINCT CountryCode,

STUFF((SELECT ',' + City

FROM @TblCity t1

WHERE t1.CountryCode = t2.CountryCode

FOR XML PATH('')), 1, 1,'') 

FROM @TblCity t2

Below is also an example to make comma separated values in a column.

But this STRING_AGG() function can be used in and above versions of SQL Server 2017

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.

SELECT CountryCode, City = STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY CountryCode)

FROM @TblCity

GROUP BY CountryCode

As compare to FOR XML PATH query, STRING_AGG() function is much faster.. detail comparison given in below link.

https://www.mssqltips.com/sqlservertip/5542/using-for-xml-path-and-stringagg-to-denormalize-sql-server-data/

TSQL query to split full name into firstname and lastname

This script only works with combination of firstname+' '+lastname.
--firstname
SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) as firstname
--lastname
SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, LEN(fullname)) as lastname