How do I handle data from a Date search in the Where clause in Postgres?

Asked

Viewed 438 times

0

I have a screen where I have a list of a company’s payment titles, and I need to do a dynamic search from the issue date of the title. The idea is that when the user enters the date in a search field, this data will be passed dynamically via AJAX to a file where there will be the query SQL.
My doubt, is how I will assemble my clause WHERE?.
Ex: WHERE data_emissao ILIKE '%2015-01-01%'
Can someone help me?

php sql ajax postgresql
  • 1

    Will use like in a date column? explains better this, between would not be more appropriate?

  • When the user enters the date, e.g.: 10/23/2015, all titles released that day will be returned in a list. Basically that’s it, I thought to use the between but this function works with two dates an initial and the other final, and in case here I only have a date. Eai?

  • And you have a problem with ? WHERE data_emissao = '2015-01-01', if you want you can pass the date as dd/mm/yyyy direct at the bank, see here

  • 1

    Yes, because in addition to the search by date has the social reason, cnpj and title number, both are treated with string and the date of issue is of type date. With this returns the following error: ERRO: sintaxe de entrada é inválida para tipo date: "2015"
SQL state: 22007

  • The doubt I have is what kind you set the column to be wanting to use ILIKE. She’s sweeping or Timestamp?

  • The user informs a complete date or only a piece, month, year etc? Puts the code you have so far, can give a lightened.

  • Follows the code: SELECT titulo.id_titulo, titulo.numero_empresa, participante.razao_social, 
 participante.cpf_cnpj, titulo.data_emissao, titulo.valor,
 titulo.autorizado
FROM titulo
INNER JOIN participante ON( titulo.id_participante = participante.id_participante )
WHERE titulo.numero_empresa ILIKE '%2015%'
OR participante.razao_social ILIKE '%2015%'
OR participante.cpf_cnpj ILIKE '%2015%'
ORDER BY id_titulo DESC
LIMIT 10 OFFSET 0;

Show 2 more comments

1 answer

2

You can compare a piece of the date with the function Extract, the first argument is desired type and the second is the name of the field.

SELECT ... WHERE data_emissao = '2015-10-23'

SELECT ... WHERE extract(year from data_emissao) = 2015

  • date_part('year', data_emissao), I think it’s simpler, he’s an alias.

  • @Guilhermelautert, I’m not a fan of extract() if I’m not mistaken he’s standard ansi, then you can carry an easy consultation with him than with date_part, ai test in mysql, select extract(year from now())

  • ah understand, you want to say that it is better to use extract, because if he changes the gang was to function normally.

  • @rray, in a way works, however, when the data comes in formats '2015-01' or a string the query fails.

  • @Exact Guilhermelautert, haha and certainly the extract() It’s kind of verbose, but there’s a reason :)

  • @Brunoduarte, is there any other format that the user can inform? beyond the two that you passed?

  • It’s not just those two formats anyway.

Show 2 more comments

Browser other questions tagged php sql ajax postgresql

You are not signed in. Login or sign up in order to post.