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 ZZZ VARCHAR(3);
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);
ZZZ = SUBSTRING(SDATE FROM 21 FOR 3);
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);
XFORMAT = REPLACE(XFORMAT, 'ZZZ', ZZZ);
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.