SQL Server so'rovi filtr natijalarini to'g'ri ko'rsatolmaydi

Quyidagi kabi SQL server so'roviga ega:

SELECT * FROM mytable 
WHERE company_name = 'Shell'
  and datetime >= '2017-03-06' 
  and datetime <= '2017-03-06' 
ORDER BY datetime DESC

Keling, ushbu kod menga barcha Shell qatorlarini qidirish davridagi vaqtni ko'rsatib turibdi.

Shell satrlarini faqat 2017-03-06 da xohlayman.

Xato qayerda?

Ushbu kod odatda MySQL-serverda yaxshi ishlaydi.

0
Bizni sinash uchun takrorlashni ta'minlay olasizmi?
qo'shib qo'ydi muallif TheGameiswar, manba
mukammal savol berishni bilish uchun havola qilingan havolani ko'ring: spaghettidba.com/2015/04/24/…
qo'shib qo'ydi muallif TheGameiswar, manba
va datetime> = '2017-03-06' va datetime <= '2017-03-06 o'rniga va datetime kabi 2017-03-06 u ishlayaptimi?
qo'shib qo'ydi muallif ahmed abdelqader, manba
Qo'shing, bizdan kamida bitta ma'lumot qatorini yaxshiroq tushunish uchun va datetime uchun qanday turdagi ma'lumotlarni kiritish kerak?
qo'shib qo'ydi muallif Armin, manba

6 javoblar

Since a datetime without a specified time segment will have a value of date 00:00:00.000, if you want to be sure you get all the dates in your range, you must either supply the time fYoKI your ending date YoKI increase your ending date and use <.

SELECT * FROM mytable WHERE company_name = 'Shell' and datetime >= '2017-03-06' and datetime < '2017-03-07' YoKIDER BY datetime DESC

YoKI

SELECT * FROM mytable WHERE company_name = 'Shell' and datetime >= '2017-03-06' and datetime <= '2017-03-06 23:59:59.999' YoKIDER BY datetime DESC
3
qo'shib qo'ydi

CAST (datetime AS DATE) va faqat tarix bilan solishtiring

SELECT * FROM mytable WHERE company_name = 'Shell' and CAST(datetime  AS DATE)= '2017-03-06' ORDER BY datetime DESC
1
qo'shib qo'ydi

Menimcha, bu sizning server sozlamalari uchun kelib chiqadi (u oy sifatida 03 kunidan foydalanmaydi, balki kun sifatida). Har doim foydalaning

Sana bilan ishlashda CONVERT (kiritishni, qaerda va h.k.). Misol uchun. Agar sana "yyyy-mm-dd" shaklida bo'lsa CONVERT (datetime, '2017-03-06', 121).

CREATE TABLE mytable (ID INT, COMPANY_NAME VARCHAR(10), DATETIME DATETIME)
SELECT * FROM mytable

INSERT INTO mytable VALUES (1,'Shell', '2017-03-06')
INSERT INTO mytable VALUES (2,'Shell', '2017-03-07')
INSERT INTO mytable VALUES (3,'Shell', '2017-03-05')
INSERT INTO mytable VALUES (4,'Shell', '2017-03-06')


INSERT INTO mytable VALUES (5,'Nut', CONVERT(datetime, '2017-03-06',121))
INSERT INTO mytable VALUES (6,'Nut', CONVERT(datetime, '2017-03-07',121))
INSERT INTO mytable VALUES (7,'Nut', CONVERT(datetime, '2017-03-05',121))
INSERT INTO mytable VALUES (8,'Nut', CONVERT(datetime, '2017-03-06',121))


---Results can be uncorrect
SELECT ID, COMPANY_NAME, DATETIME, CONVERT(varchar,DATETIME,121) AS DATE_STR
FROM mytable
WHERE company_name = 'Shell'
    AND DATETIME >= '2017-03-06'
    AND DATETIME <= '2017-03-06'
ORDER BY DATETIME DESC

---Results correct
SELECT ID, COMPANY_NAME, DATETIME, CONVERT(varchar,DATETIME,121) AS DATE_STR
FROM mytable
WHERE company_name = 'Nut'
    AND DATETIME >= CONVERT(datetime, '2017-03-06',121)
    AND DATETIME <= CONVERT(datetime, '2017-03-06',121)
ORDER BY DATETIME DESC
1
qo'shib qo'ydi

Buni qilishingiz mumkin:

SELECT * 
FROM mytable 
WHERE company_name = 'Shell' 
  and datetime >= '2017-03-06' 
  and datetime < '2017-03-07' 
ORDER BY datetime DESC

datetime "2017-03-06" ning har qanday nuqtasida joylashgan barcha qatorlarni ko'taradi.

sana ma'lumot turini Anil Kumar deb taklif qilinganda datetime = '2017-03-06' <// code> so'zini kiriting.

0
qo'shib qo'ydi
SELECT * FROM mytable WHERE company_name = 'Shell' and datetime like '2017-03-06%' ORDER BY datetime DESC
0
qo'shib qo'ydi

The most important thing about sql server. "Always consider twice what you write in where criteria" Other answers can solve your problem but the main problem is your algoritm or your logic when coding sql. What you need ? The answer of this question is "specific day" so specify just date. Do not use <>. I see you used these logic for datetime format. So convert to date and then compare. If you do not need hour, minute or something waste them. This logic is always makes simple your problems. best regards.

0
qo'shib qo'ydi