Equus Software

What to use in Custom SQL field:
<table style="height: 188px;
width: 686.665px;">


Static Value
'XXX'


Field Name and Static Value
[Table: Field Text]+'XXX'


Field name and Static Value inc. Space

Custom SQL fields - Command to a static value to a column

What to use in Custom SQL field:
DATENAME(d,[Table: Field Date]) +CASEWHEN DATENAME(d,[Table: Field Date]) IN ('1', '21', '31') THEN 'st'WHEN DATENAME(d,[Table: Field Date]) IN ('2', '22') THEN 'nd'WH

Custom SQL fields - Command to add st, nd, rd or th after a day

What to use in Custom SQL field:
DATENAME(MONTH,[Table: Field Date])
  
Example:
where [Table: Field Date] = [ASSIGNMENT!START_DATE]

 
 
 

Custom SQL fields - Command to show full name of the month

What to use in Custom SQL field (see RH column):



DD MMM YY
CONVERT(VARCHAR(11),[Table: Field Date],6)


DD MMM YYYY
CONVERT(VARCHAR(11),[Table: Field Date],106)


DD MMM YYYY hh:mi:ss:mmm
CONVERT(V

Custom SQL fields - Command to display different date formats beginning with day

See also useful SQL functions 
 
Format
NOTE: for a list of styles for date formats, see http://sqlserverplanet.com/oracleequivalents/trunc-date-sql-server/

Day first
Month first
Year first

Add st,

Custom SQL fields - List of commands for date types

What to use in Custom SQL field (see RH column):
<table style="height: 139px;
width: 653px;">


MMM DD YYYY (Time)
CONVERT(VARCHAR(19),[Table: Field Date])


MMM DD, YYYY
CONVERT(VARCHAR(12),[Table: F

Custom SQL fields - Command to display different date formats beginning with month

What to use in Custom SQL field:
ROUND(DATEDIFF(DD,[Table: Field Date1],[Table: Field Date2])/30,0)
  
Example:
where
[Table: Field Date1] = [Assignment: Start Date]
[Table: Field Date2] = [Assignment

Custom SQL fields - Command to calculate duration in months between two dates

What to use in Custom SQL field:
DATEDIFF(DD,[Table: Field Date1],[Table: Field Date2])
  
Example:
where
[Table: Field Date1] = [Assignment: Start Date]
[Table: Field Date2] = [Assignment: Actual End

Custom SQL fields - Command to calculate duration in days between two dates

What to use in Custom SQL field:
DAY(DATEADD(mm,DATEDIFF(mm, -1, [Table: Field Date]),0) -1)
 
Example:
where [Table: Field Date] = [Assignment: Start Date]

Custom SQL fields - Command to display the number of days in the month of a certain date

What to use in Custom SQL field:
DATEADD(day,X,[Table: Field Date])
DATEADD(week,X,[Table: Field Date])
DATEADD(year,X,[Table: Field Date])
 
Example:
where
X = integer (1) e.g DATEADD(day,1,[Table: F

Custom SQL fields - Command to extend date by X number (day, week, year)

What to use in Custom SQL field (see RH column):
<table style="height: 54px;
width: 648px;">


YYYY/MM/DD
CONVERT(VARCHAR(11),[Table: Field Date],111)


YYYYMMDD
CONVERT(VARCHAR(11),[Table: Field Date

Custom SQL fields - Command to display different date formats beginning with year

DBO.EQ_STD_EXCHANGE_RATE_GET(PARAMETER 1, PARAMETER 2, PARAMETER 3, PARAMETER 4)
This function will return the Company or System level exchange rate based on what has been configured on the specified

DBO function to return Exchange rate for date

DBO.EQ_STD_USER_FIRST_LAST_GET(Parameter1)
This function will return the first and last name of a user where the user_ID is contained.
 
Parameter(s)
Parameter1: Any field in the system where the user

DBO function to return first and last name from user id

DBO.EQ_STD_HOST_COUNTRY_ISO2_FROM_ASSIGNMENT_ID(Parameter1)This  function will accept the assignment id and return the 2 character ISO code for the From Country. This same information can also be adde

DBO function to return host country 2 character ISO code from assignment id

DBO.EQ_STD_CUSTOM_FIELD_CODE_FROM_UNIQUE_NAME(Parameter1, Parameter2, Parameter3)
This function will accept the display value of the custom field, the unique name of the custom field, and the company

DBO function to return lookup code from custom field with lookup values

DBO.EQ_STD_ACCOUNT_TAXABILITY_GET(Parameter1, Parameter2, Parameter3)
This function will accept Account ID, Country Code, and/or Country as parameters.  It will return the Account Taxability for an Ac

DBO function to return account taxability from account id and country

Overview

What is a DBO function?
List of useful/commonly used DBO functions
List of other DBO functions

What is a DBO function?
A DBO function is a common routine that…

Overview and List of DBO Functions

DBO.EQ_STD_USER_EMAIL_GET_BY_LOGIN(Parameter1)
This function will return the email address of a user where the user_ID is contained.
 
Parameter(s)
Parameter1: Any field in the system where the user i

DBO function to return email address from user id

DBO.EQ_STD_USER_TYPE_GET(Parameter1)
This function will return the User Type of the 'Last Update By’' and 'Create By' fields. The function will accept either the [Table Name: Last Update By] or [Table

DBO function to return user type from user id

DBO.EQ_STD_CURRENCY_NAME_FROM_CODE(Parameter1)
This function can convert any field that has the three character currency code into the currency name.
 
Parameter(s)
Parameter1: Any field in the system

DBO function to return currency name from 3 character currency code

DBO.EQ_STD_HOME_COUNTRY_ISO2_FROM_ASSIGNMENT_ID(Parameter1)This function will accept the assignment id and return the 2 character ISO code for the From Country.  This same information can also be adde

DBO function to return home country 2 character ISO code from assignment Id

DBO.EQ_STD_STATE_PROVINCE_CODE_FROM_NAME(Parameter1, Parameter2)
This function expects the country name and state/province name as inputs and will return the Code as listed on the Configuration tab, C

DBO function to return 2 letter code for country and state/province from country name and state/province name

DBO.EQ_STD_COUNTRY_CODE_FROM_NAME(Parameter1)
This function expects the country name as an input and will return the ISO 3166-1 3 Letter Code.  If you simply want the code for the Assignment From/To C

DBO function to return 3 character code from country name

DBO.EQ_STD_DEPENDENT_BASIC_INFO(Parameter1, Parameter2)
This function requires the assignment id and a date.  It will display dependent information for an assignment, including: first &amp; last name,

DBO function to return dependent details from assignment id and date

DBO.EQ_STD_USER_NAME_GET(Parameter1)
This function will return the first and last name of the current user running the report or widget.
 
Parameter(s)
Parameter1: Current user id - @SBUILDER_USER_ID

DBO function to return first and last name of current user in the system

DBO.EQ_STD_DEPENDENT_BASIC_INFO_CODES(Parameter1, Parameter2)
This function requires the assignment id and a date.  It will display dependent information for an assignment, including: first &amp; last

DBO function to return dependent details codes from assignment id and date

DBO.EQ_STD_CLASSIFICATION_CLIENT_CODE_FROM_DESCRIPTION_UNIQUE_NAME(Parameter1, Parameter2)
This function will accept the display value of a classification lookup and the unique name of the field. It w

DBO function to return lookup maintenance client code from field name with lookup values

DBO.EQ_STD_CLASSIFICATION_CODE_FROM_DESCRIPTION_UNIQUE_NAME(Parameter1, Parameter2)
This function will accept the display value of a classification lookup and the unique name of the field. It will ret

DBO function to return lookup maintenance code from field name with lookup values

DBO.EQ_STD_COMPANY_SEGMENT_CODE_FROM_NAME(Parameter1, Parameter2, Parameter3)
This function will accept the display value of the segment, the segment level, and the company id. It will return the Code

DBO function to return segment lookup code from segment display value, segment level and company id

DBO.EQ_STD_COMPANY_SEGMENT_CODE_FROM_ASSIGNMENT(Parameter1, Parameter2, Parameter3)
This function will accept the display value of the assignment id, segment level, and the value of ‘FROM’ or ‘TO’. It

DBO function to return segment lookup code from assignment id, segment level and value of 'FROM'/'TO'

DBO.EQ_STD_WEBPART_ASSIGNMENT_COORDINATOR(Parameter1, Parameter2)
This function will accept ([Assignment: ID], ‘EQ_Code’) to return all the resource contact information (including photo) based on the

DBO function to return resource contact information from assignment id and the resource code

DBO.EQ_STD_COST_ESTIMATE_LINE_ITEM_VALUE_GET(Parameter1, Parameter2, Parameter3)
This function will retrieve an annualized line item value from any cost estimate or compensation calculation.  It can b

DBO function to retrieve an annualized line item value from any cost estimate or compensation calculation

DBO.EQ_STD_COMP_DATA_REPOSITORY_AMOUNT_GET(Parameter1, Parameter2, Parameter3, Parameter4)
This function will retrieve any value from the Compensation Data Repository (CDR).  It can be used in any use

DBO function to retrieve any value from the Compensation Data Repository

DBO.EQ_STD_CONCAT_ENTRY_EXPENSE_REJECTION_GET(Parameter1)
This function is useful in displaying invoice management / expense auditing rejection notes and will accept([Entry Expense: Id] and use the be

DBO function to return comments and notes for rejected expenses for an expense entry

DBO.EQ_STD_CONVERT_NUMBER_TO_ENGLISH_WORDS
This function will convert any number to english word. This can be used to include on Merge Documents should an english word translation is needed to any num

DBO function to convert any number to English word

DBO.EQ_STD_CONVERT_NUMBER_TO_ENGLISH_WORDS
This function will convert any number to french words. This can be used to include on Merge Documents should a french word translation be needed for a number

DBO function to convert any number to French word

Definition
The COALESCE() function returns the first non-null value in a list.
 
Syntax
COALESCE(value_1, value_2, ...., value_n)
where value_1, value_2, ...., value_n are required parameters and are

COALESCE() Function

Definition
The DATEPART() function returns a specific part of a date.  This function returns the result as an integer value. 
 
Syntax
DATEPART(interval,date)
where interval is required and it is…

DATEPART() Function

Definition
The REPLACE() method searches a string for a specified value, or a regular expression, and returns a new string where the specified values are replaced.
 
Syntax
REPLACE(string, old_string,

REPLACE() Function

Definition
The SUBSTRING() function extracts some characters from a string.
 
Syntax
SUBSTRING(string, start, length)
where string is the string to extract from, start is the start position (the first

SUBSTRING() Function

Definition

The LEN() function returns the number of characters of an input string.   
Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading…

LEN() Function

Definition
The DATEADD() function adds a time/date interval to a date and then returns the date.
 
Syntax
DATEADD(interval, number, date)
where interval is required and it is the time/date…

DATEADD() Function

Definition
CHARINDEX() is a function that accepts two arguments. The first argument is the character you are searching for; the second is the string. It will return the first index…

CHARINDEX() Function

The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading…

CASE Statement Function

Definition
The GETDATE() function returns the current system timestamp as a DATETIME value without the database time zone offset. The DATETIME value is derived from the Operating System (OS) of…

GETDATE() Function

Definition
LEFT() function extracts a given number of characters from the left side of a supplied character expression.  RIGHT() function extracts a given number of characters from the right side…

LEFT() and RIGHT() Functions

Definition
The ABS() function returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)

ABS() Function

Definition
The DAY() function returns the day of the month (from 1 to 31) for a specified date.
 
Syntax
DAY(date)
where date is required and it is the date…

DAY() Function

Definition
The ROUND() function returns a numeric value, rounded to the specified length or precision.
 
Syntax
ROUND(numeric_expression , length, [function])
where 
numeric_expression is an expressio

ROUND() Function

Definition
The UPPER() function converts a string to upper-case.  The LOWER() function converts a string to lower- case.
 
Syntax
UPPER(text) 
where text is required and is the string to convert.…

LOWER() and UPPER() Functions

Definition
The DATENAME() function returns a specified part of a date.  This functions returns the result as a string value.  
 
Syntax
DATENAME(interval, date)
where interval is required and it…

DATENAME() Function

Definition
The DATEDIFF() function returns the difference between two dates.
 
Syntax
DATEDIFF(interval, date1, date2)
where interval is required and it is the part of return.  This can be one…

DATEDIFF() Function

Definition
The MONTH() function returns the month part for a specified date (a number from 1 to 12).
 
Syntax
MONTH(date)
where date is required and it is the date…

MONTH() Function

Definition
The YEAR() function returns the year part for a specified date.
 
Syntax
YEAR(date)
where date is required and it is the date to extract the year from. 
 …

YEAR() Function

Custom SQL Fields enable you to create a new field, which is the result of concatenating two or more existing fields, or is the result of a mathematical calculation on…

Useful SQL functions

Definition
The CONVERT() function converts a value (of any type) into a specified datatype.
 
Syntax
CONVERT(data_type(length), expression, style)
where 
data_type is required and is the datatype to c

CONVERT( ) Function

Definition
The CAST() function converts a value (of any type) into a specified datatype. For example, it can be used when concatenating a text and a numeric field. CAST will convert…

CAST( ) Function

Definition
The FORMAT() function formats a value with the specified format. Use the FORMAT() function to format date/time values and number values. For general data type conversions, use CAST() or CON

FORMAT() Function

Definition
The STRING_AGG function concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. The function that takes…

STRING_AGG Function

Custom SQL Fields Commands

Find answers and get help from Intercom Support and Community Experts

Conversations you've started through the messenger will appear here.

No conversations created by you

Try using different keywords or checking for typos.

No conversations found

Title

This site employs cookies and other technologies that we and our third party vendors use to monitor and record personal information about you and your interactions with the site (including content viewed, cursor movements, screen recordings, and chat contents) for the purposes described in our Cookie Policy. By continuing to visit our site, you agree to our {websiteTermsLink}, {privacyPolicyLink} and {cookiePolicyLink}.

This site uses cookies and similar technologies ("cookies") as strictly necessary for site operation. We and our partners also would like to set additional cookies to enable site performance analytics, functionality, advertising and social media features. See our {cookiePolicyLink} for details. You can change your cookie preferences in our Cookie Settings.

We use cookies to make our site work and also for analytics and advertising purposes. You can enable or disable optional cookies as desired. See our {cookiePolicyLink} for more details.

Advertising cookies are set by our advertising partners to collect information about your use of the site, our communications, and other online services over time and with different browsers and devices. They use this information to show you ads online that they think will interest you and measure the ads' performance. Social media cookies are set by social media platforms to enable you to share content on those platforms, and are capable of tracking information about your activity across other online services for use as described in their privacy policies.

These cookies enable the website to provide enhanced functionality and personalisation. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly.

These cookies are necessary for the website to function and cannot be switched off in our systems.

These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site. They help us to know which pages are the most and least popular and see how visitors move around the site.

You have the right to opt out of the sale of your personal information. See our {cookiePolicyLink} for more details about how we use your data.

Your Privacy Choices

We use cookies to enhance your experience. You can customize your cookie preferences below. See our {cookiePolicyLink} for more details.

Cookie Settings

Empty Help Center

Uh oh. That page doesn’t exist.

Home

Search results

Disappointed

Neutral

Smiley

Thinking...

Searching through sources...

Analyzing...

Tickets submitted through the messenger or by a support agent in your conversation will appear here.