Thread: PL/PGSQL
Hello everybody,
I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer.
I have two questions:
--------------------------------------------------------------------------------------------------
1.- Exception throwed by cast('yyyymmdd' as timestamp)
I do a cast conversion from a string to a timestamp. When I put a bad date on the string, the cast throw an error on my PL/PGSQL but I can't capture (I'm new). Which type of exception launch the cast function?
I put a exception space on my function but I don't know which exception I have ti capture:
EXCEPTION
WHEN exception_name THEN
statements;
Which exception _name I have to write on my code?
--------------------------------------------------------------------------------------------------
2.- Number of days on February
I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date.
I need to know if the day is correct for the month on the string, but February is different for every year. How can I know if February have 28 or 29 days depending on the year?
Thanks everybody and hello from a new user ;-)
I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer.
I have two questions:
--------------------------------------------------------------------------------------------------
1.- Exception throwed by cast('yyyymmdd' as timestamp)
I do a cast conversion from a string to a timestamp. When I put a bad date on the string, the cast throw an error on my PL/PGSQL but I can't capture (I'm new). Which type of exception launch the cast function?
I put a exception space on my function but I don't know which exception I have ti capture:
EXCEPTION
WHEN exception_name THEN
statements;
Which exception _name I have to write on my code?
--------------------------------------------------------------------------------------------------
2.- Number of days on February
I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date.
I need to know if the day is correct for the month on the string, but February is different for every year. How can I know if February have 28 or 29 days depending on the year?
Thanks everybody and hello from a new user ;-)
Attachment
Hi, Welcome aboard :) Jordi Martínez <jmartinez@uic.es> writes: > 1.- Exception throwed by cast('yyyymmdd' as timestamp) > > I do a cast conversion from a string to a timestamp. When I put a bad > date on the string, the cast throw an error on my PL/PGSQL but I can't > capture (I'm new). Which type of exception launch the cast function? The exhaustive list of error codes is there: http://www.postgresql.org/docs/8.3/static/errcodes-appendix.html It should probably be in Class 22 — Data Exception, and I'd bet one of those, depending on the faulty input: 22008 DATETIME FIELD OVERFLOW datetime_field_overflow 22007 INVALID DATETIME FORMAT invalid_datetime_format > 2.- Number of days on February > > I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date. > > I need to know if the day is correct for the month on the string, but > February is different for every year. How can I know if February have > 28 or 29 days depending on the year? The usual trick is to ask for the day number of 03/01 preceeding day: dim=# select x::date, extract(day from x::date - interval '1 day') from (values('20090301'), ('20080301'), ('20040301'), ('20000301')) as t(x); x | date_part ------------+----------- 03-01-2009 | 28 03-01-2008 | 29 03-01-2004 | 29 03-01-2000 | 29 (4 lignes) Regards, -- dim
mg>hopefully quick response
Hello everybody,
I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer.
I have two questions:
--------------------------------------------------------------------------------------------------
1.- Exception throwed by cast('yyyymmdd' as timestamp)
I do a cast conversion from a string to a timestamp. When I put a bad date on the string, the cast throw an error on my PL/PGSQL but I can't capture (I'm new). Which type of exception launch the cast function?
I put a exception space on my function but I don't know which exception I have ti capture:
EXCEPTION
WHEN exception_name THEN
MG>when VALUE_ERROR THEN
MG>DBMS_OUTPUT.PUT_LINE('Cannot convert STRING to DATE');
statements;
Which exception _name I have to write on my code?
--------------------------------------------------------------------------------------------------
2.- Number of days on February
I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date.
I need to know if the day is correct for the month on the string, but February is different for every year. How can I know if February have 28 or 29 days depending on the year?
MG>select TO_CHAR(DATE_COLUMN,'DD') FROM TABLE;
Thanks everybody and hello from a new user ;-)
Windows Live™: Keep your life in sync. Check it out.
Hello everybody,
I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer.
I have two questions:
--------------------------------------------------------------------------------------------------
1.- Exception throwed by cast('yyyymmdd' as timestamp)
I do a cast conversion from a string to a timestamp. When I put a bad date on the string, the cast throw an error on my PL/PGSQL but I can't capture (I'm new). Which type of exception launch the cast function?
I put a exception space on my function but I don't know which exception I have ti capture:
EXCEPTION
WHEN exception_name THEN
MG>when VALUE_ERROR THEN
MG>DBMS_OUTPUT.PUT_LINE('Cannot convert STRING to DATE');
statements;
Which exception _name I have to write on my code?
--------------------------------------------------------------------------------------------------
2.- Number of days on February
I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date.
I need to know if the day is correct for the month on the string, but February is different for every year. How can I know if February have 28 or 29 days depending on the year?
MG>select TO_CHAR(DATE_COLUMN,'DD') FROM TABLE;
Thanks everybody and hello from a new user ;-)
Windows Live™: Keep your life in sync. Check it out.