SQL Serverda datetime ma'lumotlarini taqqoslash

Ma'lumotlar bazasiga datetime ma'lumotlarini standart datetime sifatida saqlayman 2011-10-19 17:18: 44.083. Datetime maydoni bugungi kundan keyin 5 kunga teng bo'lsa, barcha qatorlarni tanlash kerak. Lekin men bu taqqoslashni vaqtida ham istamayman, faqat kun etarli.

SELECT * FROM dbo.TestBox
where TargetDate = dateadd(day,5, getdate()) 

Bu ishlamayapti, men nima uchun aynan o'sha vaqtlarda qarashini bilmayman.

Oldindan rahmat,

2

6 javoblar

Buni amalga oshirishingiz mumkin:

SELECT * FROM dbo.TestBox
where CONVERT(VARCHAR(8),TargetDate,112) = CONVERT(VARCHAR(8),dateadd(day,5, getdate()),112)

Yoki agar sizda TargetDate-da indeks mavjud bo'lsa:

SELECT * FROM dbo.TestBox
where TargetDate >= CONVERT(VARCHAR(8),dateadd(day,5, getdate()),112) 
AND TargetDate < CONVERT(VARCHAR(8),dateadd(day,6, getdate()),112) 
2
qo'shib qo'ydi

Agar siz 2008+ dan foydalanayotgan bo'lsangiz, har ikkalasini ham Sana sifatida foydalanishingiz mumkin:

where convert(date, targetdate) = convert(date, dateadd(day, 5, getdate()))

Agar siz 2005 yoki undan oldin foydalanmoqchi bo'lsangiz, shunday qiling:

where convert(varchar, targetdate, 101) = convert(varchar, dateadd(day, 5, getdate()), 101)
2
qo'shib qo'ydi
Ya'ni, DbType.DateTime kabi bir datetime o'zgarmaydigan .NET da o'zida saqlab turdagi protsedurani o'rnatgan bo'lsam va SQLda mos keladigan parametr datetime bo'lsa, bu ayirboshlash kerakmi?
qo'shib qo'ydi muallif MacGyver, manba

Sinash:

select * 
from dbo.TestBox 
where datediff(dd, getdate(), TargetDate) = 5

Saqlangan kod:

declare @targetDate as DateTime
set @targetDate = '2011-11-2'
select datediff(dd, getdate(), @targetDate)

Chiqish:

5

1
qo'shib qo'ydi
Nima uchun pastga tushish kerak?
qo'shib qo'ydi muallif RedFilter, manba
Format (part, start, end) bo'lib, bu GETDATE() - TargetDate ...
qo'shib qo'ydi muallif sll, manba

DATEDIFF to the rescue!

DECLARE @monday DATETIME;
DECLARE @fridayAM DATETIME;
DECLARE @fridayPM DATETIME;
SET @monday = '2011-10-23 10:50:00';
SET @fridayAM = '2011-10-28 08:00:00';
SET @fridayPM = '2011-10-28 17:30:00';
SELECT DATEDIFF(DAY, @monday, @fridayAm) AS AmDiff, 
       DATEDIFF(DAY, @monday, @fridayPm) AS PmDiff

Ham AmDiff va PmDiff 5 da bo'ladi.

1
qo'shib qo'ydi

Muammo turli xil vaqt formatlariga bog'liq bo'lishi mumkin.

This link has all the date formatting options in sql http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

Quyidagi kabi harakat qilib ko'ring:

SELECT * FROM dbo.TestBox where CONVERT(CHAR(8), TargetDate , 1)= dateadd(day,5, (CHAR(8), getdate(), 1))  

Bu sizning har ikkala tarixni bir xil formatga qiyoslaydi va vaqt qismini yo'qotadi

GETDATE() dan foydalanishingiz o'rniga siz CURRENT_TIMESTAMP dan foydalanishingiz mumkin

0
qo'shib qo'ydi

Quyidagi so'rovlar kerak bo'lgan narsani bajarishi kerak.

SELECT * FROM dbo.TestBox 
WHERE TargetDate 
        BETWEEN DATEADD(DAY, 5, DATEADD(MILLISECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))
        AND DATEADD(DAY, 5, DATEADD(MILLISECOND, -2, DATEADD(DAY, datediff(day, 0, GETDATE()) + 1, 0))) -- between the start and end of the date in 5 days
0
qo'shib qo'ydi
Men ushbu javob uchun ovoz berishni tanladim, nima sababdan sharhlash foydali bo'lardi.
qo'shib qo'ydi muallif Ant Swift, manba