home bbs files messages ]

Forums before death by AOL, social media and spammers... "We can't have nice things"

   comp.databases.ms-sqlserver      Notorious Rube Goldberg contraption      19,505 messages   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]

   Message 19,030 of 19,505   
   --CELKO-- to All   
   Re: Advanced DateDiff Calculation In SQL   
   09 Mar 14 19:08:56   
   
   From: jcelko212@earthlink.net   
      
   The Julian business day is a good trick. Number the days from whenever your   
   CREATE TABLE Calendar   
   (cal_date DATE NOT NULL PRIMARY KEY,   
    julian_business_nbr INTEGER NOT NULL,   
    ...);   
      
   INSERT INTO Calendar   
   VALUES ('2007-04-05', 42),   
    ('2007-04-06', 43), -- good Friday   
    ('2007-04-07', 43),   
    ('2007-04-08', 43), -- Easter Sunday   
    ('2007-04-09', 44),   
    ('2007-04-10', 45); --Tuesday   
      
      
   To compute the business days from Thursday of this week to next   
    Tuesdays:   
      
   SELECT (C2.julian_business_nbr - C1.julian_business_nbr)   
     FROM Calendar AS C1, Calendar AS C2   
    WHERE C1.cal_date = '2007-04-05',   
      AND C2.cal_date = '2007-04-10';   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]


(c) 1994,  bbs@darkrealms.ca