Post reply

Find this week's Friday in integer

  • March 25, 2009 at 12:05 pm

    #198353

    Today is 25th March .

    Can anyone help me to find out the output as 20090327 .i.e the week's friday in integer format yyyymmdd.

    I should be running the query in any day of the week days...but it sud find out that friday date.

  • March 25, 2009 at 12:11 pm

    #966182

    ok two steps, here's how I find "this weeks" monday and friday:

    SET DATEFIRST 1

    SELECT

    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) Monday,

    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4 Friday,

    DATEADD(ms,-3,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) + 5 ) EODFriday

    so whatever value gets returned, I would use a built in convert function to get it in YYYYMMDD format:

    FormattedDateCodeSQL

    20090325112SELECT CONVERT(VARCHAR,@date,112)

    so rolling it all up, and adding a final convert to integer:

    SET DATEFIRST 1

    SELECT CONVERT(INT,CONVERT(VARCHAR,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4 ,112))

    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!

  • March 25, 2009 at 12:18 pm

    #966187

    Why SET DATEFIRST ? whats the use of it?

  • March 25, 2009 at 1:43 pm

    #966243

    force of habit for me snuck into the code;

    SET DATEFIRST allows you to decide what day of the week is your "start of the week"; when you are fiddling with days...1=Sunday,2=Monday etc

    in this case, because I'm adding weeks and not days, it returns the same info no matter what DATEFIRST you use, whether 1 thru 7;

    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!

Viewing 4 posts - 1 through 4 (of 4 total)

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