Solved

compare timestamp to curdate() mysql

Posted on 2010-09-06
2
1,259 Views
Last Modified: 2012-05-10
Hi,
I have a column that is of type "timestamp" in format of 2010-09-04 22:24:19 .  I need to run a process where I check today's date against any records that have been created today already (marked by timestamp).  

So essentially if i do select curdate(); it'll give me back 2010-09-06.  I want to compare that to the timestamp to see if this is the same day (so ignore the hh:mm:ms).

I dont want to do substring(TS, 1,10 ) where I'll have 2010-09-04 and i'll be able to compare strings... I want to do a DATE comparison.

Is this possible somehow?

Thanks,
0
Comment
Question by:Squadless
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 500 total points
ID: 33612644
Try adding the following in where clause:
WHERE DATE(date_field) = CURDATE();
0
 
LVL 6

Expert Comment

by:nasirbest
ID: 33612733
If you want a really simple solution then don't drop hh:mm:ms part, just use it with DATE_FORMATE function and put custom value for hh:mm:ss to define day range. see following example
SELECT * FROM tbl_name WHERE 
date_column > DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND 
date_column < DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59');

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question