• Post category:BI Publisher
  • Post comments:1 Comment
  • Post last modified:June 16, 2020
  • Reading time:8 mins read
You are currently viewing Most frequently used Date, Number and String functions
Most frequently used Date, Number and String functions

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

Table of Contents

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
  • In visible box by plugintheme