Emojis in SQL in ICM InfoWorks - 6 Functions 🧮

· October 21, 2023

6 Functions 🧮

SQL expressions can utilize a myriad of functions alongside field values and constants. Each function needs a set number of 'arguments' and yields one value.

6.1 Aggregate functions 📊

Refer to Section 10.3 for details.

6.2 Date functions 📆

  • YEARPART 📅
    YEARPART(date) - Retrieves the year of the date.

  • MONTHPART 📆
    MONTHPART(date) - Determines the month of the date.

  • DAYPART 🌞
    DAYPART(date) - Extracts the day from the date.

  • DATEPART 🗓
    DATEPART(date) - Provides the date while removing minutes.

  • TIMEPART ⌚
    TIMEPART(date) - Gives the time in minutes from the date.

  • YEARMONTHPART 📅📆
    YEARMONTHPART(date) - Shows year and month as a string.

  • MONTHYEARPART 📆📅
    MONTHYEARPART(date) - Displays month and year in a string format.

  • MONTHNAME 📆
    MONTHNAME(date) - Returns the full month name.

  • SHORTMONTHNAME 🗓
    SHORTMONTHNAME(date) - Gives the abbreviated month name.

  • DAYNAME 🌞
    DAYNAME(date) - Fetches the full day name.

  • SHORTDAYNAME 🌤
    SHORTDAYNAME(date) - Offers the abbreviated day name.

  • NOW 🕰
    NOW() - Provides the current date and time.

  • YEARSDIFF 📅⏳
    YEARSDIFF(from,to) - Calculates the year difference between two dates.

  • MONTHSDIFF 📆⏳
    MONTHSDIFF(from,to) - Determines the month difference between two dates.

  • DAYSDIFF 🌞⏳
    DAYSDIFF(from,to) - Measures the day difference between two dates.

... and many more!

6.3 List variable functions 📝

Refer to the List Variables section for more details.

6.4 Trigonometric and logarithmic functions 📐📈

Covers functions related to angles and logs. Includes functions like ACOS, ASIN, ATAN, LOG, and more.

6.5 String manipulation 🎻

Delves into manipulating strings with functions like LEN, LEFT, RIGHT, MID, SUBST, etc.

6.6 Number conversion and formatting 🔢

Functions like FIXED help in formatting numbers.

6.7 Numerical functions 🔢🔍

Includes functions such as ABS, INT, FLOOR, CEIL and more for number operations.

6.7 Conditional expression 🔄

IIF and NVL functions that help in setting conditions in SQL expressions.