Most frequently used Date, Number and String functions
Most frequently used Date, Number and String functions

 602 total views

In this article we will see some of the mostly used Date, Number and String functions that we bang our heads on to get them work in SQL Query.

Want to learn Fusion Technical tools? Check out this article

Get a sequence of numbers (1-12) in a table:

We can generate sequence of numbers using the connect by expression.. We can specify the upper limit in the level condition.

SELECT ROWNUM MNTH FROM DUAL CONNECT BY LEVEL <= 12

Get Weekend dates between two input dates:

select dt date, to_char(to_date(dt),'DY') day
from (select trunc(to_date(:DATE_FROM)) + level - 1 dt from dual
      connect by trunc(to_date(:DATE_FROM)) + level - 1 <= trunc(to_date(:DATE_TO)))
where to_char(to_date(dt),'DY') in ('SAT','SUN')

Generate Random number in SQL Query:

select trunc(DBMS_RANDOM.value(100,999)) from dual
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL

Converting Comma Separated Values into Table:

The query can come quite handy when you have comma separated data string that you need to convert into table so that you can use other SQL queries like IN or NOT IN.
Here we are converting ‘AA,BB,CC,DD,EE,FF’ string to table containing AA, BB, CC etc. as each row.
Once you have this table you can join it with other table to quickly do some useful stuffs.

WITH csv AS (SELECT 'AA,BB,CC,DD,EE,FF' AS csvdata FROM DUAL)
SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL
WITH DATA AS ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual)
SELECT TRIM(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM DATA CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0

Converting Number into Word:

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL

Get the first day of the month:

Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value.

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL

Get the last day of the month:

This query is similar to above but returns last day of current month.
One thing worth noting is that it automatically takes care of leap year.
So if you have 29 days in Feb, it will return 29/2.
If you want to find the last day of a month other than the current month, replace SYSDATE with any date column/value.

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" FROM DUAL

Get the first day of the Year:

First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some calculation.

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL

Get the last day of the year:

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

Get number of days in current month:

This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL

Get number of days left in current month:

Below query calculates number of days left in current month

SELECT SYSDATE, LAST_DAY (SYSDATE) "Last", LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL

Get number of days between two dates:

Use this query to get difference between two dates in number of days

SELECT TRUNC(sysdate) - TRUNC(e.original_date_of_hire) FROM per_periods_of_service

Display each months start and end date upto last month of the year:

This query displays start date and end date of each month in current year. You might want to use this for certain types of calculations.

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date, TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
FROM XMLTABLE ('for $i in 0 to xs:int(D) return $i'
PASSING XMLELEMENT (d, FLOOR (MONTHS_BETWEEN (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),SYSDATE)))
COLUMNS i INTEGER PATH '.')

Get number of seconds passed since today (since 00:00 hr):

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL

Get number of seconds left today (till 23:59:59 hr):

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL

Get Month Names in a year:

select to_char(add_months(sysdate,LEVEL),'MON') SEQUENCE, substr(to_char(add_months(sysdate,LEVEL),'DD-MON-YY','nls_date_language=english'),4,3) MONTH  from dual
CONNECT BY LEVEL <= 12
order by SEQUENCE ASC

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has One Comment

Leave a Reply