Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Simple Date Problem

    I'm trying to find the first Monday for any given year. I've found something
    that works, but I typically find the most convoluted way to do a simple task.
    Is there something simpler/cleaner than this?

    Code:
    DECLARE @intYear INTEGER
    DECLARE @dtmFirstMon DATETIME
    DECLARE @dtmWorkDate DATETIME
    
    SET @intYear=YEAR(GETDATE())+2
    
    SET @dtmWorkDate=CAST('01/01/' +CAST(@intYear AS CHAR(4)) AS DATETIME)
    SET @dtmFirstMon=(SELECT CASE 
    			WHEN DATEPART(dw, @dtmWorkDate)=2 THEN @dtmWorkDate
    			WHEN 	DATEPART(dw, DATEADD(D,1,@dtmWorkDate))=2 THEN DATEADD(D,1,@dtmWorkDate	)
    			WHEN 	DATEPART(dw, DATEADD(D,2,@dtmWorkDate))=2 THEN DATEADD(D,2,@dtmWorkDate)
    			WHEN 	DATEPART(dw, DATEADD(D,3,@dtmWorkDate))=2 THEN DATEADD(D,3,@dtmWorkDate)
    			WHEN 	DATEPART(dw, DATEADD(D,4,@dtmWorkDate))=2 THEN DATEADD(D,4,@dtmWorkDate)
    			WHEN 	DATEPART(dw, DATEADD(D,5,@dtmWorkDate))=2 THEN DATEADD(D,5,@dtmWorkDate)
    			WHEN 	DATEPART(dw, DATEADD(D,6,@dtmWorkDate))=2 THEN DATEADD(D,6,@dtmWorkDate)
    			END)
    PRINT(@DTMFIRSTMON)
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Our old friend modulo arithmetic to the rescue:
    Code:
    DECLARE @intYear INTEGER
    SET @intYear=YEAR(GETDATE())+2
    
    DECLARE	@FirstMonday datetime
    set	@FirstMonday = cast(@intYear as char(4)) + '-01-01'
    select	dateadd(d, (9-datepart(dw, @FirstMonday)) % 7, @FirstMonday)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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