Concatenate two dates and format them in Mysql

Question

Concatenate two dates and format them in Mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have the following problem, I need to concatenate two dates and format them, the two fields of dates that I want to concatenate, I just want to show a date, since the other one is regularly empty. Some products do not have a date, so it is placed "N / A". The query is as follows:

SELECT DISTINCT 
A.codigo_producto, B.nombreproducto, date_format(A.fechakardex, '%d/%m/%Y') as fechak,
CONCAT_ws('',A.requientrada,'',A.requisalida) As referencia,
CONCAT_WS('',G.nombreservicio,'',J.nombre) As remitente,
ifnull(A.cantentrada, '0.00') as cantidade, ifnull(A.precio_entrada, '0.00') as precioE,
ifnull(TRUNCATE((A.cantentrada * A.precio_entrada),2), '0.00') AS totalentrada,
CONCAT_WS(A.fechavenci,'',A.fecha_salida) as fechav,
CONCAT_WS('',A.nolote,'',A.lote_salida)as Lote,ifnull(A.cantsalida, '0.00') as cantidads,
ifnull(A.saldo_stock, '0.00') as saldostock,
CONCAT_WS('',ifnull(A.precio_entrada, '0.00'),'',ifnull(A.precio_salida,'0.00')) as precioU,
CONCAT_WS('',F.Obersvaciones,'',h.Obersvaciones) as observaciones
FROM tbl_kardex A
LEFT JOIN tbl_productos B ON A.codigo_producto=B.codigobarras
LEFT JOIN tbl_salidas F ON A.requisalida=F.requisalida
LEFT JOIN tbl_servicios G ON F.tbl_servicios_codigoservicio= G.codigoservicio
LEFT JOIN tbl_entradas H ON A.requientrada = H.requientrada
LEFT JOIN tbl_proveedores J ON H.tbl_proveedores_codigo_proveedor=J.codigo_proveedor
WHERE A.codigo_producto='19072017A005266006'
and A.fechakardex>='2017-10-17' and A.fechakardex<='2018-10-23'

The question here is that it gives me back the date, in the following way:

2022-12-310000-00-00

I would like you to return me in the following way:

12-31-2022

and I want you to skip the date

00-00-0000

in advance thanks, I do not know much about mysql ...

Thanks for your help

mysql

asked by bendroide 04.01.2018 в 06:56

source

2 answers

Error consuming JSON. RESTful Angular app.js Integrate Jquery into JSP

score 0 · Answer 1

For what you want to do, you can use these functions

To give the format you want to date would be this way

DATE_FORMAT (date, '% j-% m-% Y');

To be able to check if the date is null or empty, you could use this

IF (col IS NULL OR col = '', 'empty', col)

Making a mixture of the two, you could have something similar to this

IF(fecha1 IS NULL OR col = '', DATE_FORMAT(fecha2,'%j-%m-%Y'), DATE_FORMAT(fecha1,'%j-%m-%Y'))

where the idea is to look if fecha1 , is empty or null, which is the case you want not to appear. In case of being empty, the second date will be formatted, which should exist in your system, otherwise the fecha1

would be formatted

score 0 · Answer 2

When executing my query I get the following:

# 1064 - Something is wrong in its syntax near 'FROM tbl_kardex A LEFT JOIN tbl_products B ON A.codigo_producto = B.codigobarras' on line 12

the query is as follows:

SELECT DISTINCT 
A.codigo_producto, B.nombreproducto, date_format(A.fechakardex, '%d/%m/%Y') as fechak,
CONCAT_ws('',A.requientrada,'',A.requisalida) As referencia,
CONCAT_WS('',G.nombreservicio,'',J.nombre) As remitente,
ifnull(A.cantentrada, '0.00') as cantidade, ifnull(A.precio_entrada, '0.00') as precioE,
ifnull(TRUNCATE((A.cantentrada * A.precio_entrada),2), '0.00') AS totalentrada,
IF(a.fechavenci IS NULL OR a.fechavenci = '', DATE_FORMAT(a.fecha_salida,'%d/%m/%Y'), DATE_FORMAT(a.fechavenci,'%d/%m/%Y')) as fvenci,
CONCAT_WS('',A.nolote,'',A.lote_salida)as Lote,ifnull(A.cantsalida, '0.00') as cantidads,
ifnull(A.saldo_stock, '0.00') as saldostock,
CONCAT_WS('',ifnull(A.precio_entrada, '0.00'),'',ifnull(A.precio_salida,'0.00') as precioU,
CONCAT_WS('',f.Obersvaciones,'',h.Obersvaciones) as observaciones
FROM tbl_kardex A
LEFT JOIN tbl_productos B ON A.codigo_producto=B.codigobarras
LEFT JOIN tbl_salidas F ON A.requisalida=F.requisalida
LEFT JOIN tbl_servicios G ON F.tbl_servicios_codigoservicio= G.codigoservicio
LEFT JOIN tbl_entradas H ON A.requientrada = H.requientrada
LEFT JOIN tbl_proveedores J ON H.tbl_proveedores_codigo_proveedor=J.codigo_proveedor
WHERE A.codigo_producto='19072017A005266006'
and A.fechakardex>='2018-01-09' and A.fechakardex<='2018-01-09'