This may be ham handed or overkill but I had the same problem, I didn't
want to TRY to put an invalid date into my database, so I wrote
CREATE FUNCTION "rick"."f_u_is_date" (varchar) RETURNS boolean AS'
-- FUNCTION f_u_Is_Date -- assumes YYYYMMDDHHMMSS
DECLARE
av_Date ALIAS FOR $1;
li_Year SMALLINT;
li_Month SMALLINT;
li_Day SMALLINT;
li_Hour SMALLINT;
li_Minute SMALLINT;
li_Second SMALLINT;
li_Days_In_Month INTEGER[12] := ''{ 31, 28, 31, 30, 31, 30, 31, 31, 30,
31, 30, 31}'';
BEGIN
-- 1 length = 14
-- 2 all digits whitespace is FATAL!
IF av_Date !~ ''^[0-9]{14}$'' THEN
-- not 14 digits
RETURN False;
END IF;
-- 3 parse
li_Year := Cast( SubStr( av_Date, 1, 4 ) AS SMALLINT );
li_Month := Cast( SubStr( av_Date, 5, 2 ) AS SMALLINT );
li_Day := Cast( SubStr( av_Date, 7, 2 ) AS SMALLINT );
li_Hour := Cast( SubStr( av_Date, 9, 2 ) AS SMALLINT );
li_Minute := Cast( SubStr( av_Date, 11, 2 ) AS SMALLINT );
li_Second := Cast( SubStr( av_Date, 13, 2 ) AS SMALLINT );
-- test date parts in range
-- and days in a month
IF ( li_Second >= 0 ) AND ( li_Second <= 59 )
AND ( li_Minute >= 0 ) AND ( li_Minute <= 59 )
AND ( li_Hour >= 0 ) AND ( li_Hour <= 23 )
AND ( li_Day >= 1 ) AND ( li_Day <= li_Days_In_Month[ li_Month ] )
AND ( li_Month >= 1 ) AND ( li_Month <= 12 )
AND ( li_Year >= 2000 )
THEN
-- date parts in range
RETURN True;
ELSE
-- February and leap year is the only exception
IF ( li_Month = 2 )
AND ( li_Day = 29 )
AND ( ( ( Mod( li_Year, 4 ) = 0 )
OR ( Mod( li_Year, 400 ) = 0 ) )
AND ( Mod( li_Year, 100 ) <> 0 ) )
THEN
-- leap year, February has 29 days
RETURN True;
ELSE
-- date parts not in range
RETURN False;
END IF;
END IF;
RETURN True;
END; -- f_u_Is_Date
'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
LitelWang wrote:
>I need this function :
>
>CheckDate('2002-02-29') return false
>CheckDate('2002-02-28') return true
>
>How to write ?
>
>Thanks for any advice .
>
>
>
