CODEWITHSUNDEEP
sqlpostgresqlfunctiongreenplum
precose
precose

Reputation: 614

to_number() returning wrong information

Using Greenplum on postgres version 8.2:

When I call this function:

CREATE OR REPLACE FUNCTION test_function(_date date)
RETURNS text AS
$BODY$
Declare  
_query text;
_date text;
BEGIN
batch_query := 'select to_number('||_date||', ''9999x99x99'')';
EXECUTE _query into _date;
RETURN _date;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

using this call:

select test_function('2012-01-15');

I'm getting 1996 as the result. I want the result to be 20120115 (which is the date that I input without the dashes)

How am I gett 1996 as a result of this function. When I input other dates I get other random "years" as well. What am I doing wrong here?

Upvotes: 0

Views: 241

Answers (1)

user330315
user330315

Reputation:

to_number() converts a string into a number (hence the name).

It does not know anything about dates.

You are calling the function with a string value, but the function is defined to accept a date, thus the string get's converted into a date value, and then it gets converted back into a string because to_number() expects one.

The to_number() function then sees: "2012 minus 1 minus 15" - which yields 1996.

If you want to pass a real date, then you are looking for the to_char() function to format your date.

Additionally you don't need PL/pgSQL for this, nor do you need dynamic SQL:

CREATE OR REPLACE FUNCTION test_function(_date date)
  RETURNS text AS
$BODY$
  select to_char(_date, 'yyyymmdd')';
$BODY$
LANGUAGE sql;

If you want to pass a string (varchar, text) value then you can use the to_number() function but you need to define the parameter as text in order to avoid the evil implicit typecast. But you should also define the function as returns numeric because that's what to_number() returns. Otherwise you again get an implicit data type conversion.

CREATE OR REPLACE FUNCTION test_function(_date text)
  RETURNS numeric AS
$BODY$
  select to_number(_date, '9999x99x99')';
$BODY$
LANGUAGE sql;

Upvotes: 3

Related Questions