CODEWITHSUNDEEP
sqlms-accesspostgresql
Awet
Awet

Reputation: 1

Converting MS Access Queries to PostgreSQL queries (format())

I have a query from MS Access SQL that I would like to execute in PostgreSQL:

SELECT Format("start_date", 'mmmm') AS "Month", 
   DFS_FIRE_ARCHIVE.FIRE_YEAR AS "Year", 
   Count(DFS_FIRE_ARCHIVE.FIRE_ZONE) AS "Number of Fires", 
   OBJECTIVES_NFD."response_category"

FROM (DFS_FIRE_ARCHIVE 

INNER JOIN OBJECTIVE_ORDER ON DFS_FIRE_ARCHIVE.OBJECTIVE = OBJECTIVE_ORDER.OBJECTIVE) 
INNER JOIN OBJECTIVES_NFD ON OBJECTIVE_ORDER.OBJECTIVE = OBJECTIVES_NFD.OBJECTIVE

GROUP BY Format("start_date",'mmmm'), 
     DFS_FIRE_ARCHIVE.FIRE_YEAR, 
     DFS_FIRE_ARCHIVE.OBJECTIVE, 
     Format("start_date",'mm'), 
     OBJECTIVE_ORDER.ORDER, 
     DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE, 
     DFS_FIRE_ARCHIVE.FIRE_TYPE, 
     OBJECTIVES_NFD."response_category"

HAVING (((DFS_FIRE_ARCHIVE.FIRE_YEAR)=2009) AND ((DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE)='INT') AND ((DFS_FIRE_ARCHIVE.FIRE_TYPE)='IFR'))

ORDER BY Format("start_date",'mm'), OBJECTIVE_ORDER.ORDER, DFS_FIRE_ARCHIVE.OBJECTIVE;

The error I am receiving is:

    ERROR:  function format(timestamp without time zone, unknown) does not exist
LINE 1: SELECT Format("start_date", 'mmmm') AS "Month", 
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any help would be appreciated, thank-you.

Upvotes: 0

Views: 339

Answers (2)

StackExchange saddens dancek
StackExchange saddens dancek

Reputation: 3378

The function you should replace Format with is probably to_char, e.g. to_char(timestamp, 'MM').

Take a look at PostgreSQL documentation: http://www.postgresql.org/docs/9.0/static/functions-formatting.html

Upvotes: 1

user330315
user330315

Reputation:

You are looking for to_char():

http://www.postgresql.org/docs/current/static/functions-formatting.html

Upvotes: 0

Related Questions