isdate_tiny

This function determines whether the input string represents a valid date with the format YYYYMMDD.

A valid date consists of exactly 8 characters, each between '0' and '9'. The month must be between 01 and 12. The days must be between '01' and the number of days in the month. Leap years are taken into account.

Syntax

isdate_tiny(date);

date: The date to be checked.
Type: VARCHAR(100)

Returns

A value of type INT4:

1 if the string is a valid date with format YYYYMMDD
NULL if the string is NULL
0 otherwise

Examples

CREATE TABLE DateAsString(date VARCHAR(50));
INSERT INTO DateAsString VALUES ('2009010');
INSERT INTO DateAsString VALUES ('200901222');
INSERT INTO DateAsString VALUES ('200');
INSERT INTO DateAsString VALUES ('-20091101');
INSERT INTO DateAsString VALUES ('20090001');
INSERT INTO DateAsString VALUES ('20091301');
INSERT INTO DateAsString VALUES ('20090400');
INSERT INTO DateAsString VALUES ('20090431');
INSERT INTO DateAsString VALUES ('20080229');
INSERT INTO DateAsString VALUES ('20090229');
INSERT INTO DateAsString VALUES ('21000229');
INSERT INTO DateAsString VALUES ('19991919');
INSERT INTO DateAsString VALUES ('20040429');
INSERT INTO DateAsString VALUES ('yyyymmdd');
INSERT INTO DateAsString VALUES ('abcd0101');
INSERT INTO DateAsString VALUES ('Date');
INSERT INTO DateAsString VALUES (NULL);

SELECT isdate_tiny(date), date FROM DateAsString ORDER BY isdate_tiny, date;

ISDATE_TINY | DATE
------------+-----------
            |
          0 | -20091101
          0 | 19991919
          0 | 200
          0 | 20090001
          0 | 2009010
          0 | 200901222
          0 | 20090229
          0 | 20090400
          0 | 20090431
          0 | 20091301
          0 | 21000229
          0 | Date
          0 | abcd0101
          0 | yyyymmdd
          1 | 20040429
          1 | 20080229
(17 rows)