SQL query to filter by date a Datetime without including the time

Question

SQL query to filter by date a Datetime without including the time

Navigation

#1 by (4 votes)
#2 by (1 votes)
#3 by (1 votes)

2

I have a query in sql that looks for some XML by date range, but I think that it is not returning results since the where is with the datetime and I only send the date .. Any suggestions?

SELECT A.e_rfc,a.fecha as fecha,C.SupplierName ,A.folio,A.r_usocfdi,A.cantidad,A.clv_prodyserv,A.descripcion,A.clv_unidad,A.valorunitario,A.importe,
    B.fecha_pago,B.forma_pago,B.moneda,B.monto,B.ImpPagado ,B.foliofiscal_rel,B.folio_rel,B.serie_rel,B.metodo_pago,B.num_parcialidad
    FROM cfdigeneral as A, cfdidetalle as B, Proveedores As C
    WHERE A.uuid=B.uuid AND A.e_rfc=C.RFC And A.fecha = @fecha

The date I send from c # and try sending only the date and sending the date in datetime but with the hours in 00

c# sql-server datetime

asked by Richard 27.08.2018 в 17:37

source

3 answers

1

Extract only the date of what comes from the BD with the function DATE() and then compare it with the format of the date you are sending

SELECT A.e_rfc,a.fecha as fecha,C.SupplierName ,A.folio,A.r_usocfdi,A.cantidad,A.clv_prodyserv,A.descripcion,A.clv_unidad,A.valorunitario,A.importe,
    B.fecha_pago,B.forma_pago,B.moneda,B.monto,B.ImpPagado ,B.foliofiscal_rel,B.folio_rel,B.serie_rel,B.metodo_pago,B.num_parcialidad
    FROM cfdigeneral as A, cfdidetalle as B, Proveedores As C
    WHERE A.uuid=B.uuid AND A.e_rfc=C.RFC And DATE(A.fecha) = @fecha
                                            //asi ....

I'll leave you a link where you will find more about Functions sql

I hope you serve, you tell me

answered by 27.08.2018 в 17:49

1

Another option that could help you is the Convert function:

This link can help you Convert and Cast Documentation

SELECT A.e_rfc,a.fecha as fecha,C.SupplierName ,A.folio,A.r_usocfdi,A.cantidad,A.clv_prodyserv,A.descripcion,A.clv_unidad,A.valorunitario,A.importe,
    B.fecha_pago,B.forma_pago,B.moneda,B.monto,B.ImpPagado ,B.foliofiscal_rel,B.folio_rel,B.serie_rel,B.metodo_pago,B.num_parcialidad
    FROM cfdigeneral as A, cfdidetalle as B, Proveedores As C
    WHERE A.uuid=B.uuid AND A.e_rfc=C.RFC And Convert(DATE,A.fecha) = @fecha

answered by 28.08.2018 в 00:19

How to enter data in the same line How can I put together a predicate function when I have to choose between several options

score 4 · Accepted Answer

Cast the date to DATE

SELECT A.e_rfc,a.fecha as fecha,C.SupplierName ,A.folio,A.r_usocfdi,A.cantidad,A.clv_prodyserv,A.descripcion,A.clv_unidad,A.valorunitario,A.importe,
    B.fecha_pago,B.forma_pago,B.moneda,B.monto,B.ImpPagado ,B.foliofiscal_rel,B.folio_rel,B.serie_rel,B.metodo_pago,B.num_parcialidad
    FROM cfdigeneral as A, cfdidetalle as B, Proveedores As C
    WHERE A.uuid=B.uuid AND A.e_rfc=C.RFC And CAST(A.fecha AS DATE) = @fecha