Clever Geek Handbook

How do I get the first day of the year and the last day of the year in SQL?

I am trying to get a set of information from my database based on datetime

. I am currently using these instructions to retrieve information:

$year_posts = $_GET['year'];
if ($_GET['year'] = date("Y")){
    $state_sql = " AND p.start_date = DATE_FORMAT(NOW() ,'%Y-01-01') and p.end_date = NOW()";
}
else{
    $state_sql = " AND p.start_date = DATE_FORMAT(NOW() ,'$year_posts-01-01') and p.end_date = DATE_FORMAT(NOW() ,'$year_posts-12-31')";
}

If my variable get = 2015. How do I make it so that I fetch all the information from 2015-01-01 00:00:00

the current date?

And as for 2014. How can I get ALL information from 2014-01-01 00:00:00

to 2014-12-31 23:59:59

?

I've tried looking at different topics, but I just can't figure it out. Any help?

+3


source to share


5 answers


I think this is what you are trying to understand.



$year_posts = $_GET['year'];
if ($year_posts == date("Y")){
    $state_sql = " AND p.start_date >= '".date('Y')."-01-01 00:00:00' and p.end_date <= '".date('Y-m-d H:i:s')."'";
}
else{
    $state_sql = " AND p.start_date >= '{$year_posts}-01-01 00:00:00' and p.end_date <= '{$year_posts}-12-31 23:59:59'";
}
+1


source


If you need to get all entries from the beginning to the end of the year, just check the year



$state_sql = " AND YEAR(p.start_date) = $year_posts and YEAR(p.end_date) = $year_posts";
+1


source


How can I get ALL information from 2014-01-01 00:00:00 to 2014-12-31 23:59:59?

With this query:

SELECT * FROM something WHERE date BETWEEN [startdate] AND [enddate]
0


source


I like to use "LIKE" for this case:

SELECT * FROM table WHERE dateCol LIKE "2014%"

OR

SELECT * FROM table WHERE DATE(dateCol) LIKE "2014%"

OR

SELECT * FROM table WHERE DATE(dateCol) LIKE "2014-__-__"

They all work.

EDIT: I saw your comment> from 2014 to today:

SELECT * FROM table WHERE DATE(dateCol) > "2014-00-00"
0


source


Try this, assuming p.start_date and p.end_date are in timestamp or datetime format.

$state_sql = "
  AND DATE_FORMAT(p.start_date) >= '$year_posts-01-01'
  AND DATE_FORMAT(p.end_date) <= '$year_posts-12-31'
";
0


source






More articles:


All Articles