Date Syntax problem - MS Query of PostgreSQL database

[Malc0Savs]

I am a relatively new to SQL queries and have been struggling for weeks to solve what I imagined to be a simple problem.

I am using MS Query from within Excel 2016 to query the company's main database. This supports PostgreSQL - I don't know which version - only that it is unlikely to have been updated since 2012.

I routinely run queries successfully - a simple example of a date criterium is:-

WHERE (pihist.description Like '%COCA%') AND (pihist.inv_date>={d '2018-05-01'})

- this happily returns all instances where the invoice date in the pihist table is equal to or greater than 1st May 2018
​

However I am wanting to select all records where the date is - say - in the past 30 days.

I have tried multiple functions without success - e.g. (pihist.inv_date>='current_date()-30') ..... BUT this returns :- "ERROR: date/time value "current" is no longer supported; Error while executing the query"

All guidance will be gratefully received.

Malc0Savs

 

[John_w]

In VBA, Date - 30 gives the date 30 days ago, therefore:

"WHERE (pihist.description Like '%COCA%') AND (pihist.inv_date>={d '" & Format(Date - 30, "yyyy-mm-dd") & "'})"

 

[Malc0Savs]

Thankyou for your helpful response. Unfortunately this generates a slightly different Error: ---- "invalid input syntax for type date: ""& Format(Date - 30, "yyyy-mm-dd") &""; Error while executing the query" --- I scrupulously copied the code you suggested and I note subtle differences between this and the error message. It certainly seems like a step forward. (Might it be the date format? In the database the format is yyyy-mm-dd ..... however when editing in MS Query and Excel the format of the criteria defaults to dd/mm/yyyy even though the output in MS Query is yyyy-mm-dd .
Malc0Savs

 

[Fazza]

hi


Does this approach work for you? https://stackoverflow.com/questions/23335970/postgresql-query-between-date-ranges


This looks helpful too https://www.postgresql.org/docs/9.2/static/functions-datetime.html


And http://www.postgresqltutorial.com/postgresql-date/
And https://robots.thoughtbot.com/better-date-manipulation-in-postgres-queries


I am unfamiliar with PostgreSQL. When I see the above links, I wonder if it is only MS Query that has created the {d '2018-05-01'} style?
The links don't use it so hopefully you don't need it either.

If it is just the format returned in Excel is the issue, that should be easy to overcome.


regards, Fazza

 

[John_w]

I just followed the syntax of your {d '2018-05-01'}, but for a variable date. From the error it seems like the database is interpreting Date (a VBA function which returns the current date) as part of the query language, which suggests you haven't got the correct string syntax.

The code I posted is the WHERE part of the full SELECT xxxx FROM yyyy WHERE ...., and is meant to be assigned to the CommandText string property of the QueryTable object containing the MS Query. Something like this (though a ListObject reference may also be needed). The sheet containing the MS Query should be the active sheet before running this code.

    Dim qt As QueryTable
    Dim cmd As String
    
    Set qt = ActiveSheet.QueryTables(1)
    cmd = "SELECT xxxx FROM yyyy " & _
          "WHERE (pihist.description Like '%COCA%') AND (pihist.inv_date>={d '" & Format(Date - 30, "yyyy-mm-dd") & "'})"
    MsgBox cmd
    qt.CommandText = cmd
    qt.Refresh

 

[Malc0Savs]

John_w and Fazza - thank you very much for your responses. I want to sit in a dark quiet room to try and work through this - it will probably be the weekend before I can do that. I may well come back, but only after I've given what you suggest a "proper go".
Regards
Malc0Savs

 

[Malc0Savs]

Solved !! With the aid of a quiet and darkened room (plus collaborating with my daughter who has similar basic knowledge as myself) .... syntax for everything in the last 30 days is :- >=(DATE(NOW())-30)
I appreciate your help.
Malc0Savs