INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...Keep up the very good job that you and your team are doing. This site has replaced my morning cup of tea as a must have/do!!!..."
Where in the world do Tek-Tips members come from?
Date and time Tips and tricks
Last week day of month
Posted: 29 Jan 10
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.
@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
-- 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
SET @dateM = DATEADD(d, -1, @dateM)
SET @intDW = DATEPART(dw, @dateM)
-- This is where the data can be saved if needed
PRINT CONVERT(VARCHAR(10), @dateM, 101)
-- Increment the month number
SET @intJ = @intJ + 1
Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close