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
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM