-2

This is my query in which i am searching for the data entered in 2005 but it shows me wrong data

image

Geshode
  • 3,600
  • 6
  • 18
  • 32
Y.m. Patel
  • 19
  • 2
  • 2
    Use `yyyy-MM-dd` instead of whatever ambiguous format you're currently using. e.g. `BETWEEN '2005-01-01' AND '2015-12-31`. BTW it's better to avoid `BETWEEN` for dates and times in SQL because it's an inclusive-range but `datetime` values are not integral, so use `dateColumn >= '2005-01-01' AND dateColumn < '2006-01-01'` instead. – Dai Sep 25 '18 at 04:03
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Sep 25 '18 at 06:32

3 Answers3

2

You can instead user the Year() function.

Do the following query:

Select title, pubdate as "Publish Date" 
From books 
Where year(pubdate) = 2005
And category = 'Computer'
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
1
SELECT title,pubdate AS "Publish Date"
    FROM books
    WHERE pubDate >= '2005-01-01' AND pubDate <= '2005-12-31'
    AND category = 'COMPUTER'

You need to use dateformat correctly yyyy-mm-dd.

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
jawahar N
  • 462
  • 2
  • 13
  • I would recommend applying date function on pubdate field, incase it is a datetime – Madhur Bhaiya Sep 25 '18 at 04:12
  • Hope, we don't need datetime function incase it is a datetime. We can able to use directly like as pubDate <= '2005-12-31 23:45' (yyyy-mm-dd HH:nn). – jawahar N Sep 25 '18 at 04:16
0

Welcome to S/O. As a newbie, you should not post images to sample your query or data. Put the actual query in your post (use spaces, not tabs for formatting). Along with your sample data too.

When querying dates in SQL, your dates are not properly formatted if they are date fields vs char. The standard format is 'yyyy-mm-dd', so if you are looking for all activity from Jan 1, 2005 to Dec 31, 2005, you could do

where
       pubDate >= '2005-01-01'
   AND pubDate < '2006-01-01'
   AND category = 'COMPUTER';

I did LESS than Jan 1, 2006 in case you ever had a date that is a date/time, it would be Dec 31 up to 11:59:59pm

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • To handle datetime fields I use [Date()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date) function on the field and use it inside where condition, incase my filtering does not depend on time. – Madhur Bhaiya Sep 25 '18 at 04:15
  • @MadhurBhaiya, I actually don't -- specifically because if you have to use a FUNCTION call, does the engine still optimize the index. By using an explicit date supported format, the index can better utilize without extra call / conversion – DRapp Sep 25 '18 at 11:46