December 15, 2003 at 2:09 pm
How do I do this? Probably pretty simple.
Get all records where the datetime field is within the last 365 days (i.e. go back 1 year from whatever day the query is run on)?
Do I have to use a "julian" date?
Thank you so much.
December 15, 2003 at 2:22 pm
SELECT yourcolumns FROM yourtable WHERE yourdatecolumn BETWEEN GETDATE()-365 AND GETDATE()
If you must take care for leap years you should use DATEADD. Works also with negative numbers as interval.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 15, 2003 at 2:23 pm
SELECT * from yourtable where DATEDIFF(day, yourdatetimecolum , getdate()) <= 365
December 15, 2003 at 2:36 pm
Guaranteed Range scan on col (if indexed)
SELECT col FROM tab WHERE col >= GETDATE() - 365
December 15, 2003 at 3:04 pm
Thanks guys.
My round-a-bout approach was:
(DATEDIFF(day, [field to compare],GetDate) < 365)
December 15, 2003 at 3:16 pm
Just a note, that query will not use any indexes; so if your table is big, it will hit the performance with a full table scan instead of index scan. Try always to have the column outside the inequation.
December 15, 2003 at 4:50 pm
You could test these also against your data (assuming you have a good set to test against) for speed.
I tend to try to not use functions in filtering unless absolutely necessary- It may offer no benefit since I do it mostly just out of habit and dont test every case
declare @Now datetime, @AYearAgo datetime
set @Now=getdate()
set @AYearAgo = dateadd(year,-1,@Now)
select Field1, Field19 from MyTable where CreateDate between @AYearAgo and @Now
--or just use the one variable
select Field1, Field19 from MyTable where CreateDate >= @AYearAgo
December 16, 2003 at 3:19 am
declare @Now char(8),
@AYearAgo char(8)
set @Now=convert(char(8),getdate(),112)
set @AYearAgo = convert(char(8),dateadd(year,-1,@Now),112)
select Field1, Field19 from MyTable where CreateDate between @AYearAgo and @Now
--or just use the one variable
select Field1, Field19 from MyTable where CreateDate >= @AYearAgo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply