Professional Web Applications Themes

Using DatePart and DateDiff together in a query - Microsoft SQL / MS SQL Server

Does any one know how I would go about using DatePArt and DateDiff in the same query for the same field? For example, I need to get the amount of days bettween two dates, which would be the DateDiff, but I don't want it to count Sat. or Sun.. Say 06/02/03 - 06/09/03, this date span includes Sat and Sun, but we don't want to count the weekend. Is this even possible? Any insight would be great. Thx....

  1. #1

    Default Using DatePart and DateDiff together in a query

    Does any one know how I would go about using DatePArt and DateDiff in the
    same query for the same field? For example, I need to get the amount of days
    bettween two dates, which would be the DateDiff, but I don't want it to
    count Sat. or Sun.. Say 06/02/03 - 06/09/03, this date span includes Sat and
    Sun, but we don't want to count the weekend.
    Is this even possible?

    Any insight would be great. Thx.


    Patty Guest

  2. #2

    Default Using DatePart and DateDiff together in a query

    You can try this:

    declare date1 datetime
    declare date2 datetime
    declare countdate datetime
    declare daycount int

    -- Set dates to work with
    set date1 = '06/02/03'
    set date2 = '06/16/03'

    set countdate = date1
    set daycount = 0

    while countdate < date2
    begin
    if datepart(dw, countdate) >= 2
    begin
    if datepart(dw, countdate) != 7
    set daycount = daycount+1

    end
    set countdate = countdate+1
    end

    -- Display datediff and the work days datediff
    select datediff(dd, date1, date2) TotalDateDiff,
    daycount WorkDaysDiff

    Edgardo Valdez

    >-----Original Message-----
    >Does any one know how I would go about using DatePArt and
    DateDiff in the
    >same query for the same field? For example, I need to get
    the amount of days
    >bettween two dates, which would be the DateDiff, but I
    don't want it to
    >count Sat. or Sun.. Say 06/02/03 - 06/09/03, this date
    span includes Sat and
    >Sun, but we don't want to count the weekend.
    >Is this even possible?
    >
    >Any insight would be great. Thx.
    >
    >
    >.
    >
    Edgardo Valdez Guest

Similar Threads

  1. DatePart, 24-hour clock
    By gary@4reach.com in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: June 26th, 04:56 PM
  2. Query - Datediff problem
    By Niles Runeberg in forum Coldfusion Database Access
    Replies: 7
    Last Post: August 19th, 05:17 PM
  3. How to SELECT DatePart as a new Variable
    By Gahiggidy in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 30th, 06:25 PM
  4. Replies: 2
    Last Post: October 16th, 03:57 PM
  5. DatePart query
    By David in forum ASP
    Replies: 3
    Last Post: September 17th, 01:11 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139