Post reply

selecting by week?

  • July 18, 2006 at 12:15 pm

    #169607

    when dealing with date values...

    what exactly is the methodology for selecting data

    by the last 4 weeks, and not by range of dates.

    the dates are in this format: 20070718

    i simply want to create a select statement which will

    pull data from the last x number of weeks.

    is there a simple way to do this?

    thanks in advance.

    _________________________

  • July 18, 2006 at 12:33 pm

    #649904

    declare

    @d datetime

    set

    @d = '20070718'

    select

    * from xxx where datefield >= dateadd(wk,-4,@d)

  • July 18, 2006 at 12:46 pm

    #649915

    so this can be automated?

    i mean if i build this into a job, do i have to go

    back and edit the (set @d = 'today') every time?

    thanks in advance.

    _________________________

  • July 18, 2006 at 12:48 pm

    #649917

    set @d = getdate()

    Regards,Yelena Varsha

  • July 18, 2006 at 12:51 pm

    #649918

    i'm getting the following error:

    Msg 8115, Level 16, State 2, Line 5

    Arithmetic overflow error converting expression to data type datetime.

    is there some thing i did wrong?

    _________________________

  • July 18, 2006 at 1:03 pm

    #649923

    One of your fileds is not a datetime!

    Make sure they both are :

    select * from xxx where datefield >= dateadd(wk,-4,getdate())


    * Noel

  • July 18, 2006 at 1:15 pm

    #649930

    [DATEFIELD]column is the following:

    Type: int

    Computed: no

    Lenth: 4

    Prec: 10

    Scale: 0

    Nullable: yes

    TrimTrailingBlanks: n/a

    FixedLenNullInSource: n/a

    Collation: null

    this is the column definition... does this help at all,

    i mean is this useful?

    _________________________

  • July 18, 2006 at 1:46 pm

    #649942

    select * from xxx where

    convert(varchar(8),datefield)

    >= dateadd(wk,-4,getdate()

    not the best solution performance-wise due to inadequate sarg (search argument)

    better would be to convert to integer value before the select statement.

    declare

    @d datetime

    declare

    @dn int

    set

    @d = dateadd(wk,-4,getdate())

    set

    @dn = convert(int, convert(varchar(4),year(@d)) + Replicate('0', 2 - len(convert(varchar(2),month(@d)))) + convert(varchar(2),month(@d))+ Replicate('0', 2 - len(convert(varchar(2),day(@d)))) + convert(varchar(2),day(@d)))

    select * from xxx where datefield >= @dn

    -

  • July 18, 2006 at 2:00 pm

    #649944

    yikes... i may be in over my head on this one.

    i thought it could be a simple one-line script

    or some thing.

    all this just to get the last 4 wks from values

    like this: 20070718

    there's no other way?

    thanks for all your help by the way.

    _________________________

  • July 18, 2006 at 2:51 pm

    #649959

    my services are no longer rendered, sorry.

  • July 18, 2006 at 3:05 pm

    #649967

    Just teasing,

    actually this is how you should do it and I'm pretty sure there is no better way

    declare @dn int

    set @dn = convert(int,(convert(varchar(8),dateadd(wk,-4,getdate()),112)))

    select * from xxx where datefield >= @dn

     

  • July 18, 2006 at 3:26 pm

    #649972

    And if you absolutely have to have it in one line

    select * from xxx where datefield >= convert(int,(convert(varchar(8),dateadd(wk,-4,getdate()),112)))

  • July 18, 2006 at 4:32 pm

    #649985

    is no problem. thanks for all your help any way

    _________________________

  • July 19, 2006 at 3:32 pm

    #650263

    Can you redefine the column to be type datetime.  It makes calculations simpler.

  • July 20, 2006 at 7:26 am

    #650368

    ken... many thanks!

    lindss...

    not really. i i'm only responsible for getting the data

    across in the quickest way possible, and can't really

    make changes to the columns, or the data.

    good question though.

    _________________________

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply