Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Last week day of month

Date and time Tips and tricks

Last week day of month

by  djj55  Posted    (Edited  )
This will give the last day on the month that is a Monday through Friday. This assumes the week is set to 1 for Sunday and 7 for Saturday. This code is for outputting only one year at a time.
Code:
DECLARE 
    @dateM DATETIME,   -- used to hold date to compare and output
    @intDW INT,        -- day of week
    @intJ INT,         -- loop control - month number
    @strD VARCHAR(10)  -- date as string

-- Start with the first month
SET @intJ = 1
-- While the month is less than 13 (Jan - Dec)
WHILE @intJ < 13
BEGIN
-- Create a string with the first day of month 
--      this could be any day within the month 
-- Notice that the code sets the year is 2010
    SET @strD = CAST(@intJ AS VARCHAR) + '/1/2010'
-- Get the last day of the month to be checked if weekday
    SET @dateM = DATEADD(month, DATEDIFF(month, 0, @strD) + 1, 0) - 1
-- Find the day of week
    SET @intDW = datepart(dw, @dateM)
-- If Sunday or Saturday subtrack one from date and check again
    WHILE @intDW = 1 OR @intDW = 7 
    BEGIN 
        SET @dateM = DATEADD(d, -1, @dateM)
        SET @intDW = DATEPART(dw, @dateM)
    END 
-- This is where the data can be saved if needed
    PRINT CONVERT(VARCHAR(10), @dateM, 101) 
-- Increment the month number
    SET @intJ = @intJ + 1 
END
Thank you,
djj
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top