i need logic to get the days for most recent week that ends with Friday

  • can some please let me kow the procedure to get the most week days(i.e the most recent firiday is 18th So i need to get the days like 12,13,14,15,16,17 and 18.

  • Something like this?

    declare @TestDate datetime;

    set @TestDate = '2009-12-19';

    select dateadd(wk,datediff(wk,0,@TestDate + 1),-9),dateadd(wk,datediff(wk,0,@TestDate + 1),-3)

    set @TestDate = '2009-12-24';

    select dateadd(wk,datediff(wk,0,@TestDate + 1),-9),dateadd(wk,datediff(wk,0,@TestDate + 1),-3)

  • dates can seem tricky until you get used to them.

    never convert a date to varchar or anything...always keep them in datetime .

    this will give you the Monday of the current week; if you need LAST monday, you'd deduct one more week from it for monday, and add 5 days to it to get that friday:

    --Monday of the Current Week

    select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

    --Monday of "Last Week":

    select DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))

    --FRIDAY of "Last Week":(Monday minus 3 days, or Monday +4

    select DATEADD(dd,-3,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))

    select DATEADD(dd,4,DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0)))

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    -Vikas Bindra

  • vikas bindra (12/21/2009)


    declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    You don't need to convert to character format. Keep your date calculations using datetime values.

  • All good answers above,

    one more way to find your last friday, (and then deduce your other days)

    Select dateadd(day, - (datepart(dw, getdate())+1), getdate())

    ---------------------------------------------------------------------------------

  • Lynn Pettis (12/21/2009)


    vikas bindra (12/21/2009)


    declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    You don't need to convert to character format. Keep your date calculations using datetime values.

    I am converting to varchar to ignore the time part and get only the date.

    Is there any other to remove the time part?

    -Vikas Bindra

  • Is there any other to remove the time part?

    Select dateadd(day, 0, datediff(day, 0, getdate()))

    ---------------------------------------------------------------------------------

  • vikas bindra (12/21/2009)


    Lynn Pettis (12/21/2009)


    vikas bindra (12/21/2009)


    declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    You don't need to convert to character format. Keep your date calculations using datetime values.

    I am converting to varchar to ignore the time part and get only the date.

    Is there any other to remove the time part?

    Don't need to ignore the time portion. For one, check out the code I posted. It doesn't care what the time portion is. Also, you drop the the time like this:

    dateadd(dd, datediff(dd, 0, getdate()), 0)

  • vikas bindra (12/21/2009)


    Lynn Pettis (12/21/2009)


    vikas bindra (12/21/2009)


    declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    You don't need to convert to character format. Keep your date calculations using datetime values.

    I am converting to varchar to ignore the time part and get only the date.

    Is there any other to remove the time part?

    Cool. Thanks for showing this. it looks better than converting to varchar

    -Vikas Bindra

  • vikas bindra (12/21/2009)


    vikas bindra (12/21/2009)


    Lynn Pettis (12/21/2009)


    vikas bindra (12/21/2009)


    declare @lastFriday datetime

    SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

    select

    @lastFriday - 6

    ,@lastFriday - 5

    ,@lastFriday - 4

    ,@lastFriday - 3

    ,@lastFriday - 2

    ,@lastFriday - 1

    ,@lastFriday

    You don't need to convert to character format. Keep your date calculations using datetime values.

    I am converting to varchar to ignore the time part and get only the date.

    Is there any other to remove the time part?

    Cool. Thanks for showing this. it looks better than converting to varchar

    You can also check out my blog post Some Common Date Routines.

  • Thanks Lynn!

    -Vikas Bindra

  • I have got the expected result, but i need to display the header like Dec 18 2009, dec 17 2009.....dec 12 2009..Can you please help me to get in this format

  • How about you show us what you have tried first. Maybe we'll what you are doing wrong and help you that way instead of just giving you the answer.

  • vikas bindra (12/21/2009)


    Cool. Thanks for showing this. it looks better than converting to varchar

    Over a fair number of rows, it's also quite a bit faster because it doesn't have to go through a string based conversion.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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