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

Question

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?

**

What data type is `OrderDate`? In this expression `(CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY))` you are missing the "closing" single quote for `'1 day'`. The expression also doesn't make sense at all - it will always return the _number_ 1. What are you trying to achieve with that? — , Nov 14 '15 at 11:10
If it is a date, then why are you comparing it to a string? You probably want a simple `orderdate >= current_date - 1` if you want to get everything after "yesterday" — , Nov 14 '15 at 11:14
i want to achieve from my current_date always it provides 'MM-01-2015' FOR EX 11-14-2015 will be 11-01-2015. — NEO, Nov 14 '15 at 11:14
You just said the data type for `orderdate` is `date` - a `date` doesn't have a "format" and you sure can't have a string like `'MM-01-2015'` in a `date` column. `current_date` is a function in Postgres that returns well, the current date. You can't have anything like `'MM-01-2015'` "in" that either — , Nov 14 '15 at 11:16
i tried test=# select TO_CHAR((CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY')) ,'MM-DD-YYYY') ; to_char ------------ 00-01-0000 (1 row) — NEO, Nov 14 '15 at 11:21
but we want outcome as 11-01-2015.not sure if this is way to convert function in postgres. also posted sql server query convert function here. — NEO, Nov 14 '15 at 11:22
Yes, I will make try on this date_trunc function.let you know the outcome if it is solve my purpose. thanks — NEO, Nov 14 '15 at 11:26

score 1 · Accepted Answer · 2015-11-14T11:37:04.117

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}

I just tested and implemented in my script at test database . it seems like solving my purpose. Thanks a ton. — NEO, Nov 14 '15 at 11:32
@vickps: I added an explanation on what your expression is doing as you seem to have completely misunderstood how Postgres handles dates, timestamps and intervals — , Nov 14 '15 at 11:38
This expanantion seems useful info for all people who is novice in postgresql. Thanks — NEO, Nov 14 '15 at 12:19
@vickps: depends on where you come from. An Oracle user will not find this surprising. — , Nov 14 '15 at 12:23

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

1 Answers1