CODEWITHSUNDEEP
phpmysqldatetime
Gjert
Gjert

Reputation: 1067

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

I am trying to fetch a set of information from my database based on datetime. I am currently using these statements to fetch 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 get variable is = 2015. How do I make it so I fetch all the information from 2015-01-01 00:00:00 to current date?

And same goes for 2014. How can I fetch ALL information from 2014-01-01 00:00:00 to 2014-12-31 23:59:59?

I've tried looking it up on different threads, but I simply can't figure it out. Any help?

Upvotes: 3

Views: 371

Answers (5)

aCarella
aCarella

Reputation: 2578

Try this, assuming that 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'
";

Upvotes: 0

Kailash Yadav
Kailash Yadav

Reputation: 1930

I think this is what you trying to figure out. 

$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'";
}

Upvotes: 1

C4d
C4d

Reputation: 3292

I like using "LIKE" for that 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-__-__"

All of them are working.

EDIT: I've seen your comment > from 2014 till today:

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

Upvotes: 0

Pedro Lobito
Pedro Lobito

Reputation: 99041

How can I fetch 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]

Upvotes: 0

Volkan Ulukut
Volkan Ulukut

Reputation: 4228

If you need the get all records 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";

Upvotes: 1

Related Questions