June 23, 2003 at 1:55 pm
In T-SQL, how do I pull just yesterdays data between specific times from a system that stores the data in mm/dd/yyyy hh:mm:ss. I tried using the DateDiff, but it returns yesterday's and todays data.
Thanks for any help you can give.
June 24, 2003 at 12:31 am
Try using the "between" Operator.
For example in Pubs Database
select * from pubs..sales where ord_date between '1994-09-13 00:00:00.000' and '1994-09-14 00:00:00.000'
June 24, 2003 at 6:26 am
I need the date to be dynamically set to yesterday, or today -1.
June 24, 2003 at 6:30 am
Hi rmilford,
quote:
today -1.
what about
declare @mydate datetime
set @mydate = getdate()-1
print @mydate
?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 24, 2003 at 6:35 am
SELECT * FROM MyTable
WHERE MyDateField BETWEEN
DATEADD(D, -1, DATEDIFF(D, 0, GETDATE()))
AND
DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
June 24, 2003 at 6:38 am
Just noticed you said your date field actually stored time portion too. Change to this:
SELECT * FROM MyTable
WHERE DATEADD(D, 0, DATEDIFF(D, 0, MyDateField)) BETWEEN
DATEADD(D, -1, DATEDIFF(D, 0, GETDATE()))
AND
DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
[/quote]
June 24, 2003 at 6:40 am
I have done this with success:
WHERE mydate > CONVERT(CHAR(8), GETDATE()-1,1) + ' 05:00:00.000'
and mydate < CONVERT(CHAR(8), GETDATE(),1) + ' 05:00:00.000'
You can change the time to be whatever time you desire, just notice there's a space between the ' and the first digit of the time.
-SQLBill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply