473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

check date validity

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
2 5124
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: TN Bella | last post by:
Trying to check if an entry for an date is < 3 years to current date or > 30 days from current date. How can I do this in asp.net....? Do I need to use the customvalidation. Can anyone give me an...
1
by: Thomas | last post by:
Hi all, i have to check if a value is a valid date format depending on a "format string" the user specified. For example: Format String: "dd/mm/yyyy" Date Value: "01/12/2006" Valid: Yes! ...
1
by: Rich | last post by:
Hello, I have been adding a handler to the TextBox.Leave event to check if a value that is entered in a Date Textbox is a Date. This works OK. But I am just checking if there is anything...
1
by: slynch401k | last post by:
I am trying to import a paradox table into an access database. I need to do this daily as I get a download of this file everyday. I want to do this on startup of a form. How can I check the date...
2
by: wizard | last post by:
Hello Friends, There is a PHP function checkdate to check if a date is a valid Gregorian date. Is there any such function to check for a valid time like something in hh:mm:ss format? If not, can...
2
by: jayakrishnanav | last post by:
Can any one help me with the js code to check whether the data entered in a text-field (for date),is in correct date format tat is "dd-mm-yyyy"? Thanks in Advance jk
10
by: viki1967 | last post by:
Help with check date and hours Hi all. I have this form: <html> <head>
2
by: divyac | last post by:
I posted this same question previously to generate form fields dynamically but now i have got one more doubt..pls help me..I am doing an inventory project using PHP and in Sales/Rentals page there is...
4
by: viki1967 | last post by:
Hi all. I have this javascript function: function isDate(str) { spz = str.split("/"); if (spz.length != 3) return(false);
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.