By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

check date validity

P: n/a
I need this function :

CheckDate('2002-02-29') return false
CheckDate('2002-02-28') return true

How to write ?

Thanks for any advice .

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 .


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2

P: n/a
On Fri, 2004-01-16 at 06:58, Harald Fuchs wrote:
In article <20**************************@smtp.vip.163.com>,
"LitelWang" <wl***@vip.163.com> writes:
I need this function :
CheckDate('2002-02-29') return false
CheckDate('2002-02-28') return true


Why would you want to do that? Just try to insert '2002-02-29' into
your DATE column, and PostgreSQL will complain.


But it won't complain usefully. It will just abort the transaction.
It's difficult to determine what went wrong when Postgres craps out,
which is at least in part why many on this list recommend duplicating
all the database validation logic in your application for EVERY type.

To me, this seems like a waste of effort, since both the application and
the DB server have to confirm that every date (for example, but applies
to every other type as well) is valid. But I can't see how to do it any
other way, since the prevailing consensus among the PG devs seems to be
that any problem with the values of data is an application problem, not
a database problem, so don't expect to get any help from the server
other than "Sorry, that transaction is now gone. Hope you can reproduce
the work! Have a nice day."

Thanks,
b.g.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.