FirebirdSQL 2.x: DATE_TO_CHAR Procedure (like Oracle's TO_CHAR)
Tue Nov 21 2023 18:25:25 GMT+0000 (Coordinated Universal Time)
Saved by
@marcopinero
#javascript
CREATE OR ALTER PROCEDURE DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100)) RETURNS (
RESULT 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);
RESULT = XFORMAT;
SUSPEND;
END;
content_copyCOPY
Comments