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