CODEWITHSUNDEEP
mysqlsqlhibernatedatetimecriteria
Samrat Indra
Samrat Indra

Reputation: 13

Unable to compare Date

I want to fetch today's records from database. Column in table have data type Datetime. So whenever i try to compare records with today's date, actual comparison done with date + time so unable to retrieve data.How to compare only date?

Upvotes: 1

Views: 187

Answers (4)

Pavan Baddi
Pavan Baddi

Reputation: 479

To retrieve records based on present date and time you need php date() function. HERE YOU WILL GET BRIEF INFORMATION ABOUT INSERTING AND DISPLAYING DATA AS PER CURRENT DATE.

Here is the process you can follow: Inserting Data

if(isset($_GET['send'])){
    $name = $_GET['name']."<br>";
    $phone = $_GET['phone']."<br>";

    date_default_timezone_set("Asia/Calcutta");//SET TIMEZONE

    $current_time = date('h:i:sa');//STORE CURRENT TIME IN VAR
    $current_date = date('Y-m-d');//STORE CURRENT DATE IN VAR

    $insert = "INSERT INTO datewise (name, phone, date, time) VALUES ('$name','$phone','$current_date','$current_time')"; 

    if($link->query($insert) === TRUE){
        echo "inserted";
    }
    else
    {
        echo $insert."<br>".$link->error;
    }

}

Displaying Data as per the current date inside table

<table>
        <tr>
            <td>id</td>
            <td>name</td>
            <td>phone</td>
            <td>date</td>
            <td>Time</td>
        </tr>
    <?php
        $presentDate =  date('Y-m-d'); //THIS VAR STORES CURRENT DATE AND COMPARES IT WITH DATABASE
        $disp = "SELECT * FROM datewise WHERE date='$presentDate'";

        $disp_res = $link->query($disp);

        while($row = $disp_res->fetch_assoc()){
    ?>
        <tr>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo $row['name']; ?></td>
            <td><?php echo $row['phone']; ?></td>
            <td><?php echo $row['date']; ?></td>
            <td><?php echo $row['time']; ?></td>
        </tr>
    <?php

        }
    ?>
    </table>

Upvotes: 0

Vijayakumar Udupa
Vijayakumar Udupa

Reputation: 1145

You have two solutions for same.

  1. extract date from your date column and compare to today's date [Answer suggested by GurV] It has performance impact when you have large number of rows in your table. Function to extract date from datetime type column will have to be executed for every record.
  2. Use range functions like BETWEEN clause. so, query should look like

your_column between CURDATE() and CURDATE()+ INTERVAL 1 DAY

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

Do a date comparison like this:

where date(your_column) = date(now())

This approach extracts the date portion of the timestamp from both your date column and the current timestamp (represented by NOW()).

Upvotes: 2

Rohit Gupta
Rohit Gupta

Reputation: 455

Cast you DateTime column to date Like The below Code

Select * from You Date Where Cast(date,DateTimeColumn)=Cast(Date,GetDate())

Upvotes: 0

Related Questions