Clever Geek Handbook

How to use BETWEEN mysql date and time if date and time with different field in table

I have a table reservasi

with structural data:

  • id_reservasi

  • package_name

    <- varchar
  • date_check_in

    <- varchar format (dmY)
  • time_check_in

    <- varchar format (h: i: s)
  • price

    <- varchar

I want to select data with a filter between date and time from previous data reservasi

before the specified time.

I am currently using this sql query:

SELECT SUM(price) AS total
FROM reservasi 
WHERE date(str_to_date(date_check_in,'%d-%m-%Y')) BETWEEN date(str_to_date('$date_check_in_rekap','%d-%m-%Y')) AND date(str_to_date('$date_now','%d-%m-%Y') 
AND STR_TO_DATE(time_check_in, '%H:%i:%s') BETWEEN STR_TO_DATE('$time_check_in_rekap', '%H:%i:%s') AND STR_TO_DATE('$time_now', '%H:%i:%s')

But it doesn't show any data ... How to use it correctly BETWEEN

?

+3


source to share


2 answers


edit, PDO version:

$cek_pembelian=$this->pdo->prepare("
    SELECT 
        SUM(netto_bersih) AS netto_beli, 
        SUM(jumlah_dibayar) AS rupiah_beli 
    FROM p_penerimaan_tbs 
    WHERE str_to_date(CONCAT(tanggal,' ',jam_keluar) ,'%d-%m-%Y %H:%i:%s') 
        BETWEEN str_to_date(:datetime_start ,'%d-%m-%Y %H:%i:%s') AND now()
        ");
$cek_pembelian->execute(array(
    ':datetime_start'=>$tanggal_rekap .' '. $jam_rekap
    ));

Is this what you want I think?

SQL Fiddle



MySQL 5.6 Schema setup :

Request 1 :

SELECT SUM(price) AS total
FROM t 
WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s')
BETWEEN '2017-01-24 18:20:33' AND now()

Results :

| total |
|-------|
|   300 |
+2


source


Thanks for the request from @Blag

Perfect operation ...



Edited Mysql query:

SELECT SUM(price) AS total FROM t WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s') BETWEEN str_to_date('$date_check_in_previous $time_check_in_previous') AND now()

0


source






More articles:


All Articles