Oracle SQL Date Conversions

In this post, we’re exploring the solutions other date formats than we have. For example, when we have DD/MM/YYYY format, what if we need…

In this post, we’re exploring the solutions other date formats than we have. For example, when we have DD/MM/YYYY format, what if we need only the year of this date?

Extracting year, month and day from given date (or column in a table) we can use EXTRACT() function. Although SYSDATE (or any date column with time information) contains the necessary time values, it is not possible to extract extract hour, minute and second from a date formatted column. To get those values, format needs to be TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE or INTERVAL DAY TO SECOND. I will not cover TIMESTAMP and other types. Mostly I do not work these types on daily basis (or even yearly basis 🙂)

SELECT SYSDATE AS SYSTEM_DATE
        ,EXTRACT(YEAR FROM SYSDATE) AS EXTRACT_YEAR
        ,EXTRACT(MONTH FROM SYSDATE) AS EXTRACT_MONTH
        ,EXTRACT(DAY FROM SYSDATE) AS EXTRACT_DAY
FROM DUAL
;

It is also possible to get year, month and day values from date using TO_CHAR() function. With TO_CHAR(), it is more like a type conversion and extracting at the same time.

SELECT SYSDATE AS SYSTEM_DATE 
        ,TO_CHAR(SYSDATE, 'YYYY') AS EXTRACT_YEAR_V2
        ,TO_CHAR(SYSDATE, 'MM') AS EXTRACT_MONTH_V2
        ,TO_CHAR(SYSDATE, 'DD') AS EXTRACT_DAY_V2
FROM DUAL
;

As seen above, result are a little bit different for month values. When we use EXTRACT(), output is 8, for TO_CHAR() it is 08. We can't use 'M' format because it is invalid for month extraction. This is also applicable for day values too. If current date would be 5th of June and the output would be 05: TO_CHAR(TO_DATE('05062022','DDMMYYYY'), 'DD').

Conversion Format Table (When the current date is 14/08/2022)

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Show hidden characters

	#	Code	Result	Explanation
	1	TO_CHAR(SYSDATE, 'DDMMYYYY')	14082022	Lately my favorite format 😃
	2	TO_CHAR(SYSDATE, 'YYYYMMDD')	20220814
	3	TO_CHAR(SYSDATE, 'YYYYMM')	202208
	4	EXTRACT(YEAR FROM SYSDATE) || EXTRACT(MONTH FROM SYSDATE)	20228
	5	EXTRACT(YEAR FROM SYSDATE) || LPAD(EXTRACT(MONTH FROM SYSDATE), 2, '0')	202208
	6	TO_CHAR(SYSDATE, 'YYYY')	2022
	7	TO_CHAR(SYSDATE, 'MONTH')	AUGUST	Month Name (Result is based on NLS Settings)
	8	TO_CHAR(SYSDATE, 'MON')	AUG	Month Name (Short)
	9	TO_CHAR(SYSDATE, 'MM')	08	If month value < 10 then, it takes zero before the value
	10	TO_CHAR(SYSDATE, 'MM') * 1	8	Eliminating the zero before value - v1
	11	TO_NUMBER(TO_CHAR(SYSDATE, 'MM'))	8	Eliminating the zero before value - v2
	12	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = TURKISH')	AĞUSTOS	Local Month Name - v1
	13	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = GERMAN')	AUGUST	Local Month Name - v2
	14	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = FRENCH')	AOÛT	Local Month Name - v3
	15	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE')	8月	Local Month Name - v4
	16	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = SPANISH')	AGOSTO	Local Month Name - v5
	17	TO_CHAR(SYSDATE, 'DAY')	SUNDAY	Day Name (Result is based on NLS Settings)
	18	TO_CHAR(SYSDATE, 'DD')	14	If day value < 10 then, it takes zero before the value
	19	TO_CHAR(SYSDATE, 'DD') * 1	14	Eliminating the zero before value - v1
	20	TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))	14	Eliminating the zero before value - v2
	21	TO_DATE(2021 || LPAD('08',2,0) || LPAD('02',2,0), 'YYYYMMDD')	02/08/2021	Creating Date (If you replace 2021, 08 and 02 with year, month and day values you want, you can create date)

view raw date-conversions.csv hosted with ❤ by GitHub

Note: You might need to click view raw link to see all columns in the list (GitHub Gist) above.