SQL and T-SQL string and date time functions (FORMAT, STUFF, DATALENGTH, PATINDEX, SWITCHOFFSET)

String Functions

FORMAT: you can format an input value based on a format string, and optionally specify the culture as a third input where relevant. You can use any format string supported by the .NET Framework.

STUFF: The STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.

LEFT / RIGHT : The LEFT and RIGHT functions extract a requested number of characters from the left and right ends of the input string, respectively. 

DATALENGTH : The DATALENGTH function returns the length of the input in terms of number of bytes.

String Concatenation: Handling null value.

PATINDEX : T-SQL also supports a function called PATINDEX that, like CHARINDEX, you can use to locate the first position of a string within another string. But whereas with CHARINDEX you’re looking for a constant string, with PATINDEX you’re looking for a pattern.

Other String functions:

Date and Time Functions

GETDATE and CURRENT_TIMESTAMP both same GETDATE is T-SQL function and CURRENT_TIMESTAMP is standard SQL.

SYSDATETIME and SYSDATETIMEOFFSET are similar, only returning the values as the more precise DATETIME2 and DATETIMEOFFSET types (including offset), respectively.

There are no built-in functions to return the current date or the current time: CAST(SYSDATETIME() AS DATE).

GETUTCDATE function returns the current date and time in UTC terms as a DATETIME type, and SYSUTCDATE does the same, only returning the result as the more precise DATETIME2 type.

With the SWITCHOFFSET function, you can return an input DATETIMEOFFSET value in a requested offset term. Consider the expression SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00'). Regardless of the offset of the instance you are connected to, you request to present the current date and time value in terms of offset '-08:00'. If the system’s offset is, say, '-05:00', the function will compensate for this by subtracting three hours from the input value.

TODATETIMEOFFSET : You use it to construct a DATETIMEOFFSET value from two inputs: the first is a date and time value that is not offset aware, and the second is the offset. You can use this function when migrating from data that is not offset-aware, where you keep the local date and time value in one attribute, and the offset in another, to offset-aware data.

Other data time functions:

Cheers!