Post reply

SQL Statement Question - Ghuru needed!

  • December 15, 2003 at 2:09 pm

    #358775

    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

    #486263

    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

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    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

    #486265

    SELECT * from yourtable where DATEDIFF(day, yourdatetimecolum , getdate()) <= 365

  • December 15, 2003 at 2:36 pm

    #486269

    Guaranteed Range scan on col (if indexed)

    SELECT col FROM tab WHERE col >= GETDATE() - 365

  • December 15, 2003 at 3:04 pm

    #486273

    Thanks guys.

    My round-a-bout approach was:

    (DATEDIFF(day, [field to compare],GetDate) < 365)

  • December 15, 2003 at 3:16 pm

    #486274

    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

    #486282

    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

    #486339

    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