Re: [SQL] dateformat issue
On Thursday 10 April 2008 09:41, Gavin 'Beau' Baumanis wrote:
> Hi Aarni,
>
> just so you know I am using the CF built-in function,
> createodbcdate
>
> here is the info from livedocs.
>
> CreateODBCDate
> Description
>
> Returns a date in ODBC date format.
> Category
>
> Date and time functions
> Syntax
>
> CreateODBCDate(date)
>
> so I pass in 2008-01-23
> and I get back
>
> {d '2008-01-23'}
>
> The weirdness for me is that it works elsewhere in the application, I
> am really stumped.
>
> -Gavin.
>
That value shouldn't work at all ... ?
#Now()# ||
#CreateODBCDate(2008-04-10)# = #CreateODBCDate(1994)#
|
#CreateODBCDate('2008-04-10')#
|
#CreateODBCDate(Now())#
|
#DateFormat(Now(), "-MM-DD")#
|
#DateFormat('2008-04-10', "-MM-DD")#
Produces:
{ts '2008-04-10 10:26:21'} || {d '1905-06-16'} = {d '1905-06-16'} | {d
'2008-04-10'} | {d '2008-04-10'} | 2008-04-10 | 2008-04-10
This is on CFMX7.
Best regards,
--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dateformat issue
Gavin 'Beau' Baumanis escribió:
Hi Everyone,
I have a question about dates, please.
I am using Coldfusion and Postgres
I have a function that takes the ate entered in a form and converts it
into an odbc date format.
So the string ends up being;
{d '2008-04-10'}
Strangely, the function is used throughout the application and causes
no issues but on one particular template.
And this same templates is replicated for other clients - that
seemingly have no issue .
The SQL is simply
Insert into table (datefield) values ({d '2008-04-10'})
Now, I have read the docs and do not see the {d '2008-04-10'} format
as being a valid date data type...
But it works elsewhere in the application / on other sites - thus my
current state of confusion.
Thanks in advance for any ideas you might have.
Please contact me if you should have any questions.
Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.
E: [EMAIL PROTECTED]
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au
I had some similar probles with dates, and i solved it using a
string format (mmdd) to set date format, and two functions (toString
and toDate) made by myself inf the necesary language (asp, php, jsp)
This format is full valid for comparations (>, <, =) and orders in
SQL. The only problem is to work in your application, but it may be
solved with some functions
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pl/PgSQL, variable names in NEW
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 |> I don't want to rewrite whole trigger to plPerl as I would have to use |> DBD-PgSPI. | | Huh? Certainly not -- there are functions in PL/Perl for this. See | spi_exec_query in | http://www.postgresql.org/docs/8.3/static/plperl-database.html Oh, I see. I have read the doc "...can be done via the function spi_exec_query described below, or via an experimental module DBD::PgSPI...", but missed the "OR" and thought that DBD::PgSPI is mandatory. Thanks. Martin - -- Regards, Martin Edlman Fortech, spol. s r.o, Ropkova 51, 57001 Litomyšl Public GPG key: http://edas.visaci.cz/#gpgkeys -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFH/fstqmMakYm+VJ8RAjLSAKCW4rqz2w1CaND8/BxAxLcOlwwziQCgjxsA PAAPqST5r5St08OgJsVkVK4= =ZUKV -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dateformat issue
Hi,
Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> writes:
> I have a question about dates, please.
>
> I am using Coldfusion and Postgres
> I have a function that takes the ate entered in a form and converts it
> into an odbc date format.
>
> So the string ends up being;
> {d '2008-04-10'}
>
> Strangely, the function is used throughout the application and causes
> no issues but on one particular template.
> And this same templates is replicated for other clients - that
> seemingly have no issue .
>
>
> The SQL is simply
>
> Insert into table (datefield) values ({d '2008-04-10'})
>
> Now, I have read the docs and do not see the {d '2008-04-10'} format
> as being a valid date data type...
> But it works elsewhere in the application / on other sites - thus my
> current state of confusion.
>
> Thanks in advance for any ideas you might have.
As others have underlined, "{d '2008-04-10'}" is not a valid SQL
timestamp format. I think your query adapter somehow converts it to its
appropriate format on the fly. If I were you, to spot the problem, I'd
turn query logging on in the server side and see
- How does working date queries get passed to the server?
- How does bogus query get passed to the server?
This details can help you/us to identify the problem.
Regards.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pl/PgSQL, variable names in NEW
Martin Edlman wrote: > |> I don't want to rewrite whole trigger to plPerl as I would have to use > |> DBD-PgSPI. > | > | Huh? Certainly not -- there are functions in PL/Perl for this. See > | spi_exec_query in > | http://www.postgresql.org/docs/8.3/static/plperl-database.html > > Oh, I see. I have read the doc "...can be done via the function > spi_exec_query described below, or via an experimental module > DBD::PgSPI...", but missed the "OR" and thought that DBD::PgSPI is > mandatory. Yeah, that's a bit confusing. I don't know why we have a mention of DBD::PgSPI on the plperl manual at all. Is there anything it can do that can't be done with PL/Perl native calls? Question for plperl hackers: Should we remove the mention of DBD::PgSPI from the PL/Perl manual? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [HACKERS] [SQL] pl/PgSQL, variable names in NEW
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Question for plperl hackers: Should we remove the mention of DBD::PgSPI > from the PL/Perl manual? It seems like a reasonable suggestion to me, since perl database users probably already know DBD and don't have to learn something new if they go that way. Possibly the text should be reworded, with the mention of DBD::PgSPI put somewhere else or stuck into a or something. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [DOCS] [HACKERS] [SQL] pl/PgSQL, variable names in NEW
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > From what I can see on CPAN (unless I am missing something) DBD::PgSPI > hasn't been updated since 2004 and is at version 0.2. Oh, if it's not a live project then that changes things entirely. +1 for just dropping the mention. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [DOCS] [HACKERS] [SQL] pl/PgSQL, variable names in NEW
Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > From what I can see on CPAN (unless I am missing something) DBD::PgSPI > > hasn't been updated since 2004 and is at version 0.2. > > Oh, if it's not a live project then that changes things entirely. > +1 for just dropping the mention. Done. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Testing for null record in plpgsql
Ok, let's say I have the following:
CREATE TABLE foo (
val1 integer,
val2 integer
);
CREATE OR REPLACE FUNCTION returns_null_maybe()
RETURNS foo AS $$
DECLARE
res integer;
BEGIN
SELECT INTO res extract('month' from now()::integer % 2;
IF res == 0 THEN
RETURN NULL;
ELSE
RETURN (5,5)::foo;
END IF;
END;
$$ LANGUAGE plpgsql;
Now, let's say I want to call this from another function and test the
result to see if I have a null record (null, null),. I've got the
following working but it feels like there should be something much
simpler but I just can't seem to hit on it. Is this it?
CREATE FUNCTION test_null_rec()
RETURNS boolean AS $$
DECLARE
res boolean;
null_rec foo;
BEGIN
SELECT INTO res * FROM returns_null_maybe();
IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
