FirebirdSQL: DATE_TO_CHAR Function (like Oracle's TO_CHAR)

CREATE OR ALTER FUNCTION DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100)) RETURNS VARCHAR(100) AS DECLARE SDATE VARCHAR(100); DECLARE DD VARCHAR(2); DECLARE MM VARCHAR(2); DECLARE YY VARCHAR(4); DECLARE HH VARCHAR(2); DECLARE MI VARCHAR(2); DECLARE SS VARCHAR(2); DECLARE XFORMAT VARCHAR(100); DECLARE AM VARCHAR(2); BEGIN XFORMAT = UPPER(FORMAT); SDATE = CAST(TDATE AS VARCHAR(100)); YY = SUBSTRING(SDATE FROM 1 FOR 4); MM = SUBSTRING(SDATE FROM 6 FOR 2); DD = SUBSTRING(SDATE FROM 9 FOR 2); HH = SUBSTRING(SDATE FROM 12 FOR 2); MI = SUBSTRING(SDATE FROM 15 FOR 2); SS = SUBSTRING(SDATE FROM 18 FOR 2); XFORMAT = REPLACE(XFORMAT, 'YYYY', YY); XFORMAT = REPLACE(XFORMAT, 'MM', MM); XFORMAT = REPLACE(XFORMAT, 'DD', DD); XFORMAT = REPLACE(XFORMAT, 'YY', SUBSTRING(YY FROM 3 FOR 2)); XFORMAT = REPLACE(XFORMAT, 'HH24', HH); AM = 'AM'; IF (HH='12') THEN BEGIN AM = 'M'; IF (MI > '00') THEN BEGIN AM='PM'; END END IF (HH='00') THEN BEGIN HH='12'; AM='AM'; END IF (HH>'12') THEN BEGIN HH = TRIM(CAST(CAST(HH AS INTEGER)-12 AS VARCHAR(2))); IF (CHAR_LENGTH(HH)<2) THEN BEGIN HH='0'||HH; END AM='PM'; END XFORMAT = REPLACE(XFORMAT, 'HH12', HH); XFORMAT = REPLACE(XFORMAT, 'HH', HH); XFORMAT = REPLACE(XFORMAT, 'MI', MI); XFORMAT = REPLACE(XFORMAT, 'SS', SS); RETURN XFORMAT; END; /* Examples: SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS') FROM RDB$DATABASE; SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH12:MI AM') FROM RDB$DATABASE; SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YY HH24:MI') FROM RDB$DATABASE; -- still not complete... */

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.