On 07/12/2003 16:16 [EMAIL PROTECTED] wrote:
Hello,
I need to create a view in Postgres that has a where clause of the date < beginning of month.
i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC
As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this?
I do shed-loads of these date-related queries and although it's feasible to write some SQL/UDF function to do what you're asking, in my experience it is better to process the date in your app and pass it across as a parameter. That way you could use the same piece of SQL to get, for example, data which is > month owing just by passing 2003-11-01 as the date. Probably what you need is to write a function which takes an arbitary date and returns the first date in that month/year. You _could_ write this as PostgreSQL User Defined Function but writing it as part of your app will give you a) greater flexibility as the function will be easily available to other parts of your application b) if your app language/dev environment has a source-level debugger, you will be able to benefit from it when debugging your function and c) someone trying to maintain your app in 4 years time will only need to know your application language, SQL and a possible (very!) few PostgreSQL-specific variations from the SQL language definition.
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller Business |
| Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
