MySQL Date comparison

The simplest use of the Date_format function for comparison

The code is as follows	Copy Code
SELECT * from TB where c> date_format (' 2007-07-06 ', '%y%m%d ') and C <= date_format (' 2007-07-09 ', '%y%m%d '); SELECT * from TB where c> date (' 2007-07-07 ') and c< date (' 2007-07-09 ')

Statday is a Date data field of type 2006031001

The code is as follows	Copy Code
SELECT * from Loginstat where statday> date_format (Curdate ()-1, '%y%m%d ') and Statday >= Date_format (curdate (), '%Y% M%d ');

Or:

The code is as follows	Copy Code
SELECT * from Loginstat where statday> date_format (Curdate ()-1, '%y%m%d%h ') and Statday >= (Date_format (), '% Y%m%d%h ');

Other uses:

The code is as follows	Copy Code
SELECT * from Loginstat where Statday >= date_format (' $date 1 ', '%y%m%d%h ') and Statday < Date_format (' $date ', '%y%m%d %H ')

The time format for the MySQL database is 2008-12-28 18:08:08, and now you have to get a date time from one result set Rs. I first try to get the time with the Rs.getdate () method, the result is only month day, can not get hours, minutes and seconds. The Final Solution is:

The code is as follows	Copy Code
Date Time1=new Date (Rs.gettimestamp ("Pub_time"). GetTime ()); SimpleDateFormat formattime = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); String Pubtime=formatime.format (time1);

The obtained pubtime is a string and is converted in the SQL statement using MySQL's time function date_format (' Times ', ' format '):

The code is as follows	Copy Code
String sqlstr= "SELECT * from Do_document where Pub_time<date_format (' +pubtime+" ', '%y-%m-%d%h:%i:%s ') Order by pub_ Time desc limit 0,1 ";

The SQL statement is then executed to find the record that satisfies the condition.

Share Three

1.

The code is as follows	Copy Code
SELECT * FROM table name WHERE field name BETWEEN ' yyyy-mm-1 ' and ' yyyy-mm-30 ';

Date-time function can be used to further amend the given date
DateTime and date-type data can be directly compared, and datetime-type data is automatically converted to date-type data when compared.

2. Function

The code is as follows	Copy Code
SELECT * From table Where year (date field name) =2007 and month (date field name) =6 and day (date field name) =10

My own common use is the following method

A unix_timestamp letter from MySQL б????? br/> For example

The code is as follows	Copy Code
Unix_timestamp (' 2008-08-08 20:08:08 '); Unix_timestamp (' 2008-08-08 ');

The return value is the whole of the root of the disaster benzene pupa p

Instance

code is as follows	copy code
Select * From table where Createtime>unix_timestamp (' 2011-1-1 12:12:12 ');