+

Addition

-

Subtraction

*

Multiplication

/

Division

%

Modulus. This provides the remainder after dividing the first numeric expression by the second one.

For example 9 % 3 = 0 (as 9 is divisible by 3, with no remainder).

While 9 % 2 = 1 (as 2 goes into 9 4 times, with 1 remaining).

=

Equals

> 

Greater Than

< 

Less Than

<> 

Not Equal To

And

Logical And. Performs logical conjunction on two Boolean expressions.

Or

Logical Or. Performs logical disjunction on two Boolean expressions.

Not

Logical Not. Performs logical negation on a Boolean expression.

( )

Opening/Closing bracket. Used in conjunction with group statements in an expression and apply an order of precedence in which the statements are evaluated.

RTRIM (value)

Removes any trailing whitespace from text

e.g. RTRIM([ATDesc])

LTRIM (value)

Removes any leading whitespace from text

e.g. LTRIM([ATDesc])

SUBSTRING (Value, Start, Length)

Returns the requested section of text

e.g. SUBSTRING(‘MyString’,1,2)

DATEPART (Part, Date)

Returns a number representing the specified part of the date. See date formats below.

e.g. DATEPART(M,[TSDate])

LEN(Value)

Returns the number of characters within the supplied text

e.g. LEN(‘MyString’)

ISNULL (Value, With)

If the value is null it is replaced 

e.g. ISNULL(NULL,’X’)

IIF (Expression, True, False)

If the expression is true replace with the “True” value else replace with the “False” value

e.g. IIF([COCode]=’CST001’,’A’,’B’)

CONVERT (Type, Input [, Style])

Converts one data type to another. See Data types below. See Styles below.

e.g. CONVERT(MONEY,123456)

e.g. CONVERT(DATETIME,’DD/MM/YYYY’,103)

CASE

   WHEN condition 1 THEN result 1

   WHEN condition 2 THEN result 2

ELSE

   result 3

END

Evaluates a number of conditions and returns the appropriate result

IN (item1, item2, …)

Is the value one of the items listed

e.g. IIF([COCode] IN ('CST001', 'CST002'), 'YES', 'NO')

EXISTS

LIKE(String)

Does the value match the supplied string

% Any number of characters

_ Any single character

[] Any single character within the range or set e.g. [a-f] or [abcdef]

[^] Any single character not within the range or set e.g. [^a-f] or [^abcdef]

e.g. IIF([COCode] LIKE ('CST%'), 'YES', 'NO')

NOT

Reverses the value of a Boolean expression

e.g. IIF([COCode] NOT LIKE ('CST%'), 'YES', 'NO')

OR

Returns True if any of the compared conditions are True

e.g. IIF([COCode]='CST001' OR [COCode]='CST002', 'YES', 'NO')

AND

Returns True if all the compared conditions are True

e.g. IIF([COCode]='CST001' AND [TSIsInvoiced]=TRUE, 'YES', 'NO')

BETWEEN value1 AND value2

returns True if the test value is greater than or equal to value1 and less than or equal to value2

e.g. IIF([TSYear] BETWEEN 2015 AND 2020,'YES', 'NO')

DISTINCT

GETDATE ()

Returns the system date

e.g. GETDATE()

TRUE

Used to check the status of a Boolean field

e.g. IIF([TSChargeable]=TRUE,’YES’,’NO’)

FALSE

Used to check the status of a Boolean field

e.g. IIF([TSChargeable]=FALSE,’YES’,’NO’)

CONCAT ( string_value1, string_value2, string_valueN ] ) 

Concatenate fields together to create a single string e.g. CONCAT([Start Date], ‘:’, [Project Name], ‘:’ [Total]) for the result 10/01/2020:My Project:1234

BIGINT

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

INT

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

SMALLINT

-2^15 (-32,768) to 2^15-1 (32,767)

TINYINT

0 to 255

BIT

An integer data type that can take a value of 1, 0, or NULL.

DECIMAL

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

NUMERIC

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

MONEY

Data types that represent monetary or currency values.

FLOAT

Approximate-number data types for use with floating point numeric data

REAL

DATETIME

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

SMALLDATETIME

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

CHAR

Fixed-size string data

VARCHAR

Variable-size string data

TEXT

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647)

NCHAR

Fixed-size string data

NVARCHAR

Variable-size string data.

NTEXT

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes

BINARY

binary [(n)] Fixed-length binary data with a length of n bytes

1

US

mm/dd/yy

101

US

mm/dd/yyyy

2

ANSI

yy.mm.dd

102

ANSI

yyyy.mm.dd

3

British / French

dd/mm/yy

103

British / French

dd/mm/yyyy

4

German

dd.mm.yy

104

German

dd.mm.yyyy

5

Italian

dd-mm-yy

105

Italian

dd-mm-yyyy

6

dd mon yy

106

dd mon yyyy

7

Mon dd, yy

107

Mon dd, yyyy

8, 24 or 108

hh:mi:ss

9 or 109

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

US

mm-dd-yy

110

US

mm-dd-yyyy

11

Japan

yy/mm/dd

111

Japan

yyyy/mm/dd

12

ISO

yymmdd

112

ISO

yyyymmdd

13 or 113

Europe Default + Milliseconds

dd mon yyyy hh:mi:ss:mmm (24h)

14 or 114

hh:mi:ss:mmm (24h)

DAYOFYEAR

DY, Y

DAY

DD, D

WEEK

WK, WW

WEEKDAY

DW

MONTH

MM, M

QUARTER

QQ, Q

YEAR

YY, YYYY

HOUR

HH

MINUTE

MI, N

SECOND

SS, S

MILLISECOND

MS

MICROSECOND

MCS

NANOSECOND

NS

TZOFFSET

TZ

ISO_WEEK

ISOWK, ISOWW