Results 1 to 8 of 8

Thread: Qry

  1. #1
    Join Date
    May 2008
    Posts
    3

    Cool Unanswered: Qry

    Can any body send me the query to find the date of the last friday of the month in a single query...

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SELECT MAX(dt) dt
    FROM Calendar
    WHERE YearNum = 2008
    AND MonthName = 'December'
    AND DayName = 'Friday' ;

    You have a calendar table of course? :-)

  3. #3
    Join Date
    May 2008
    Posts
    3
    Quote Originally Posted by dportas
    SELECT MAX(dt) dt
    FROM Calendar
    WHERE YearNum = 2008
    AND MonthName = 'December'
    AND DayName = 'Friday' ;

    You have a calendar table of course? :-)
    Hi i could not find a table calender.
    In which database do we have

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @d		DATETIME
    
    SET @d = GetDate()
    
    SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
    SET @d = DateAdd(d, (6 - DatePart(dw, @d) % 7) - 7, @d)
    
    SELECT @d, DatePart(dw, @d)
    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh noes!!!
    Code:
    DECLARE @d        DATETIME
    
    SET DATEFIRST 1
    
    SET @d = GetDate()
    
    SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
    SET @d = DateAdd(d, (6 - DatePart(dw, @d) % 7) - 7, @d)
    
    SELECT @d, DatePart(dw, @d)
    
    SET DATEFIRST 2
    
    SET @d = GetDate()
    
    SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
    SET @d = DateAdd(d, (6 - DatePart(dw, @d) % 7) - 7, @d)
    
    SELECT @d, DatePart(dw, @d)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Excellent point. Wear a hat.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you - I shall.

    Minor alteration - Datepart independent:
    Code:
    DECLARE @d        DATETIME
    
    SELECT    @@datefirst
    
    SET DATEFIRST 1
    
    SET @d = GetDate()
    
    SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
    
    SET @d = DateAdd(d, ((6 - @@datefirst) - DatePart(dw, @d) % 7) - 7, @d)
    
    SELECT @d, DatePart(dw, @d)
    
    SET DATEFIRST 2
    
    SET @d = GetDate()
    
    SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
    SET @d = DateAdd(d, ((6 - @@datefirst) - DatePart(dw, @d) % 7) - 7, @d)
    
    SELECT @d, DatePart(dw, @d)
    BTW - I see you are using 0 as a date. Coooo.....


    EDIT - oops - that's wrong too. I need to dig out some old code....
    Last edited by pootle flump; 12-02-08 at 06:46.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The use of zero and the DATEFIRST adjustment both add nicely to the NZDF effect!
    Code:
    DECLARE @d		DATETIME
    DECLARE @i		INT
    
    SET @d = GetDate()
    SET @i = 7
    
    WHILE 0 < @i
       BEGIN
          SET DATEFIRST @i
    
          SET @d = DateAdd(m, 1 + DateDiff(m, 0, @d), 0)
          SET @d = DateAdd(d, (14 - @@datefirst - DatePart(dw, @d)) % 7 - 7, @d)
          SELECT @d, @i
    
          SET @i = @i - 1
       END
    -PatP
    Last edited by Pat Phelan; 12-02-08 at 08:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •