Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Next Working Day PLPGSQL Function

I am trying to write a PLPGSQL function which will return the next working day for a given date. However when I try to run the query:

select next_working_day('16/06/2008'); it gives me the error
ERROR:  syntax error at or near "$1" at character 37
I have tried different date formats incase it was wanting it in the format yyyy/mm/dd etc but still the same error
Can anyone see where I am going wrong here?
DECLARE
       dowCheck RECORD;
       timeCheck ALIAS FOR $1;
       nextWorkingDay INTEGER;
BEGIN
 
SELECT extract(dow FROM TIMESTAMP timeCheck) AS dow INTO dowCheck;
 
IF dowCheck.dow>4 THEN
   dowCheck.dow = 1;
ELSE
    dowCheck.dow = (dowCheck.dow + 1);
END IF;
return dowCheck.dow;
END;

Open in new window

0
tricl
Asked:
tricl
  • 2
1 Solution
 
tusharkanvindeCommented:
CREATE OR REPLACE FUNCTION temp(timestamp)
  RETURNS int2 AS
$BODY$

SELECT cast((case when extract(dow FROM  $1)>4 then 1 else extract(dow FROM  $1)+1 end) as int2) AS dow INTO dowCheck;

$BODY$
  LANGUAGE 'sql' VOLATILE;

The function parameter is a timestamp and not a string
0
 
triclAuthor Commented:
Thanks for the fast reply, I have one more function which is suffering similar problems:
I have tried using a cast to convert the result which I guess is a double into in2 to find out if it is between 0 and 6 however I get the same $1 error.

Obviously I will accept your solution, but could you have a quick look at this other query within the points I am awarding?

Cheers
SELECT cast(extract(DOW FROM TIMESTAMP $1) as int2) AS doweek INTO dowCheck;
 
IF (dowCheck.doweek>0) AND (dowCheck.doweek<6) THEN
   RETURN TRUE;
ELSE
   RETURN FALSE;
END IF;

Open in new window

0
 
tusharkanvindeCommented:
Glad it works for you. Try this

CREATE OR REPLACE FUNCTION doweek("timestamp")
  RETURNS bool AS
$BODY$
select case when  extract(dow from $1) between 1 and 5 then true else false end as doweek;
$BODY$
  LANGUAGE 'sql' VOLATILE;
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now