Search notes:

SQL Server - T-SQL / functions

cast

cast converts a value from one data type to another.

convert

convert returns the value of an expression in a different data type.

collationproperty

select
   table_schema,
   table_name,
   column_name,
   collation_name,
   collationproperty(collation_name, 'CodePage') code_page,
   collationproperty(collation_name, 'LCID'    ) locale_identifier
from
   information_schema.columns;

dateadd

dateadd is used for date arithmetic.

The following example adds 10 days to August 28th, 2018:

declare @dt as date = '2018-08-28';
print (dateadd(d, 10, @dt)); -- 2018-09-07

Github repository about-MSSQL, path: /t-sql/functions/dateadd.sql

See also the equally named VBA function dateAdd.

datediff

dateDiff(datePart, startDate, endDate) returns the duration between startDate and endDate in non-fractional datePart units.

datePart is an unquoted literal and cannot be a variable:

`yy`, `yyyy`	year
`qq`, `q`	quarter
`mm`, `m`	month
`dy`, `y`	dayofyear
`dd`, `d`	day
`wk`, `ww`	week
`hh`	hour
`mi`, `n`	minute
`ss`, `s`	second
`ms`	millisecond
`mcs`	microsecond
`ns`	nanosecond

select
   datediff(day, '2020-03-05', '2020-03-12') -- 7 (days)

datename

select
  datename(yyyy, sysdatetime()) year       ,
  datename(mm  , sysdatetime()) month      ,  -- Returns name of month, not its number!
  datename(d   , sysdatetime()) day        ,
  datename(hh  , sysdatetime()) hour       ,
  datename(mi  , sysdatetime()) minute     ,
  datename(ss  , sysdatetime()) second     ,
  --
  datename(dy  , sysdatetime()) day_of_year	
;

Github repository about-MSSQL, path: /t-sql/functions/datename.sql

See also the three functions year(), month() and day().

db_name

If called without arguments, this function returns the current database name.

If called with an id, it returns the nam of the database that is identified with this id (see sys.databases).

iif

iif(condition, value-if-true, value-if-false)

The following select statement replaces the value 3 with 99:

create table tq84_iif (
   txt varchar(10),
   num int
);

insert into tq84_iif values ('one'  , 1);
insert into tq84_iif values ('two'  , 2);
insert into tq84_iif values ('three', 3);
insert into tq84_iif values ('four' , 4);

select
   txt,
   iif(num = 3, 99, num) -- Replace 3 with 99
from
  tq84_iif;

Github repository about-MSSQL, path: /t-sql/functions/iif.sql

format

format() creates strings from different data types with a specific format (which sort of comes closest to the functionality of printf or strftime.

isNull

isNull(expr, replacement) evaluates to the value of expr if expr is not null and to the value of replacement if expr is null.

select
   isNull( null , 'replaced') as   returns_replaced,
   isNull('xyz' , 'replaced') as   regturns_xyz
;

len

len(expr) returns the string-length of expr.

object_definition

see here.

object_id

see here.

openquery

openquery(LinkedSrv, 'select …') executes a pass-through query on the specified linked server (first parameter).

A new linked server can be added with sp_addLinkedServer, the names of the currently added linked servers can be determined with sp_linkedServers.

openrowset

openrowset allows to query data from an OLE DB capable data source.

round

round()

scope_identity

ident_current and @@identity

server_property

see here.

sql_variant_property

sql_variant_property() allows to inspect properties of an sql_variant data type.

I found the function especially useful to determine the data type of any expression.

str

str(expr) / str(expr, len) / str(expr, len, dec-places)

declare
   @dec_7_2  decimal(7, 2) = 42.1234;

print '>' + str(@dec_7_2        ) + '<'; -- >        42<
print '>' + str(@dec_7_2, 5     ) + '<'; -- >   42<
print '>' + str(@dec_7_2, 5, 1  ) + '<'; -- > 42.1<

Github repository about-MSSQL, path: /t-sql/functions/str.sql

string_split

string_split split a string and create a table with one column that has the parts of the splitted each in one row.

stuff

stuff(string, start_pos, length, replace_string) replaces length characters from start_pos in string with replace_string.

stuff can be used to remove the first n characters from a string: stuff(string, 1, n, '').

select
  stuff('1234567890', 5, 2, 'abcd'), -- 1234abcd7890
  stuff('1234567890', 5, 0, 'abcd'), -- 1234abcd567890
  stuff('1234567890', 5, 2, ''    ), -- 12347890
  stuff('1234567890', 1, 4, ''    )  -- 567890

substring

substring returns the part of a string which is indicated by its (1-based) starting position (2nd argument) and its length (3rd argument).

The SQL server equivalent of Oracle's substr. Unlike Oracle's substr however, substring requires all three parameters.

select substring('1234567890', 5, 3) --> 567

suser_sname

suser_sname returns the login name associated with a security identification number (SID). When called without the optional parameter, it returns the name of the current security context.

Compare with suser_name()

sysdatetime

sysdatetime (and sysutcdatetime()) are the datetime2 equivalents of getDate() and getUTCDate().

xact_state

xact_state() can be used to determine if a session (?) has an active transaction.