Query of date check

Asked

Viewed 277 times

-2

I have a normal query: 

select * from TABELAS 
    Where (AlvaraNumero  <>  '0') 
    and ( AlvaraValidade <> '0000-00-00' ) 
    and ( AlvaraAnexo is  NOT Null ) 
    and ( AcidenteNumero <> '0') 
    and ( AcidenteValidade  <>  '0000-00-00' ) 
    and ( AcidenteValidade <> '0000-00-00' ) 
    and ( SeguroNumero <> '0') 
    and ( SeguroValidade <> '0000-00-00') 
    and ( FinancasValidade <> '0000-00-00') 
    and ( FinancasAnexo is NOT Null) 
    and ( SocialValidade <> '0000-00-00') 
    and ( SocialAnexo is NOT Null) 
    and ( RemuneracaoValidade <> '0000-00-00' ) 
    and ( RemuneracaoAnexo is not  Null) 
    and ( InstaladorNumero  <>  '0') 
    and ( InstaladorValidade <> '0000-00-00' ) 
    and ( InstaladorAnexo is Not Null) 
    and ( MedicaValidade <> '0000-00-00') 
    and ( MedicaAnexo is Not Null) 
    order by tb_trabalhador.id asc

How to add to all fields that have Validity at the end because they are dates that cannot be Null or have already passed the date of today?
If you are past the date you can no longer appear in this query.

query

  • Are you using any high-level language or is it just SQL?

  • I am using SQL. The simplest.

  • It was not said which DBMS, but : use a DATE type for the columns of dates , if the BD has use CHECK CONSTRAINTS or TRIGGERS for validations of the date type smaller/larger than the current , use the CONSTRAINT NOT NULL some mandatory fields.

  • MSQL WORKBENCH 6.0

  • the answer below is not enough for what you want? Could you give more details about what does not meet?

1 answer

3

In the example below will return only records with date less than the current date and time.

In Oracle:

SELECT * FROM TABELA WHERE CAMPODATA IS NOT NULL AND CAMPODATA < SYSDATE

In SQL Server:

SELECT * FROM TABELA WHERE CAMPODATA IS NOT NULL AND CAMPODATA < GETDATE()

In DB2:

SELECT * FROM TABELA WHERE CAMPODATA IS NOT NULL AND CAMPODATA < (CURRENT TIMESTAMP)

In Mysql:

SELECT * FROM TABELA WHERE CAMPODATA IS NOT NULL AND CAMPODATA < NOW()

  • and use with Mysql?

  • 1

    added to Mysql. Watch out whether the date and time is UTC or not because NOW returns in the current Timezone.

Browser other questions tagged query

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