SQL Server datetime convert function equivalent to postgresql (pivotal hawq)

We have following SQL script in SQL Server 2012. We are about to write similar script in postgresql (HAWQ 1.3.1) at database conversion

SELECT * 
FROM tablename_1 
LEFT OUTER JOIN
     (SELECT 
          SUM(b.OrderValue) AS OrderValue, b.OrderDate, b.Description 
      FROM 
          (SELECT * 
           FROM tablename_2 rcd
           LEFT JOIN 
                (SELECT Distinct 
                     afv.Item, afv.Description, afd.KeyField
                 FROM tablename_3 afd
                 JOIN tablename_3 afv ON afv.FormType = afd.FormType 
                                      AND afv.FieldName = afd.FieldName 
                                      AND afv.Item = afd.AlphaValue
                 WHERE
                      afd.FormType = 'CUS'
                      AND afd.FieldName = 'COR002') a ON a.KeyField = rcd.Customer 
           WHERE
               OrderDate >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()), 101)) b 
    GROUP BY
        b.OrderDate, b.Description) c ON rtr.CorpAcctName = c.Description

We tried and wrote the following script:

Above script compiled into postgresql ( VERSION HAWQ 1.3.1)

SELECT * from tablename_1  rtr LEFT OUTER JOIN
(SELECT SUM(b."OrderValue") as OrderValue,b."OrderDate", b."Description" from
(SELECT *  from tablename_2 rcd
LEFT JOIN 
( SELECT Distinct afv."Item", afv."Description", afd."KeyField"
FROM tablename_2 afd
  Join tablename_3 afv on afv."FormType" = afd."FormType" and afv."FieldName"=afd."FieldName" and afv."Item"=afd."AlphaValue"
Where   afd."FormType" = 'CUS'and afd."FieldName" = 'COR002') a
ON a."KeyField" =rcd."Customer" where "OrderDate">=TO_CHAR((CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY)) ,'MM-DD-YYYY')) b 
group by b."OrderDate", b."Description") c
on rtr."CorpAcctName"=c."Description"

Also tried with :

When we tried to convert ms sql server convert function into postgres for orderdate column comparison of OrderDate must reflect as 'MM-01-YYYY'(desired result) which is actually coming '00-01-0000' not desired.instead we are looking for outcome as '11-01-2015'

What will be the convert() function expression in postgresql for getting desired result?

Solution

i wanted to achieve first day of month from my current_date

So you want everything that was created after the start of the current month.

A simple

where "OrderDate" >= date_trunc('month', current_date)

will do that.

Details on the date_trunc() method can be found in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html

You should understand what your expression TO_CHAR((CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY')) ,'MM-DD-YYYY') is doing so that you avoid that error in the future:

First: current_date - interval '1 day' subtracts an interval from a date, which yields a timestamp: "yesterday" at 00:00:00.

Then you subtract that from today's date, so current_date - timestamp '2015-11-13 00:00:00.0' (if today is 2015-11-14).

This yields an interval: 0 years 0 mons 1 days 0 hours 0 mins 0.00 secs

You then pass that interval to the to_char() function which formats the passed interval. As it only has "1 day", no year, no month, the result of applying the format string 'MM-DD-YYYY' on that does indeed yield 00-01-0000.

You then compare this character value against a real date - which is also something you should not do.

^{You should really get rid of those dreaded quoted identifiers. They are much more trouble than they are worth it}