CODEWITHSUNDEEP
phpsql-serverdatetime
Jishad P
Jishad P

Reputation: 77

MSSQL datetime Field No getting Between Result

I am working a blog web application that having PHP + MSSQL.

Blogs Table ...

  • Pk_blog_id
    • // Integer PK
  • Blog_title
    • // String
  • Blog_content
    • // Text
  • Created_at
    • // DateTime

    I want to collect data from table with months.

    my query is.

    $first_day= date('Y-m-01', $month);   // Getting First day of Month
$last_day = date('Y-m-t', $month);   // Getting Last Day of Month

$sql = "SELECT 
    Pk_blog_id,
    Blog_title,
    Blog_content,
    Created_at 
    FROM dbo.tb_IEAG_Blogs 
    WHERE Is_archived = 0 
    AND Created_at >= CONVERT(datetime,'$first_day') 
    AND Created_at < CONVERT(datetime,'$last_day') 
    ORDER BY tb_IEAG_Blogs.Pk_blog_id DESC";

    I am not getting result as expected...

    When echo the query is like below...

    SELECT 
 Pk_blog_id,
 Blog_title,
 Blog_content,
 Created_at 
 FROM dbo.tb_IEAG_Blogs 
 WHERE Is_archived = 0 
 AND Created_at >= CONVERT(datetime,'2016-02-01') 
 AND Created_at < CONVERT(datetime,'2016-02-28') 
 ORDER BY tb_IEAG_Blogs.Pk_blog_id DESC

    Is that is possible the BETWEEN in MSSQL ?

    Thanks....

    Upvotes: 0

    Views: 53

    Answers (1)

    Kason
    Kason

    Reputation: 797

    SELECT 
 Pk_blog_id,
 Blog_title,
 Blog_content,
 Created_at 
 FROM dbo.tb_IEAG_Blogs 
 WHERE Is_archived = 0 
 AND cast(Created_at as date) >= '2016-02-01'
 AND cast(Created_at as date) <'2016-02-28'
 ORDER BY tb_IEAG_Blogs.Pk_blog_id DESC

    Try this. Because you are comparing the datetime, 2016-02-01 datetime is not equal to your datetime in table.

    If you want select the month, here a better SQL for you.

       SELECT 
     Pk_blog_id,
     Blog_title,
     Blog_content,
     Created_at 
     FROM dbo.tb_IEAG_Blogs 
     WHERE Is_archived = 0 
     AND DATEPART(MONTH, CAST(GETDATE() AS DATETIME)) = 2
     ORDER BY tb_IEAG_Blogs.Pk_blog_id DESC

    Upvotes: 1

    Related Questions