Professional Web Applications Themes

Strange date criteria. - Microsoft SQL / MS SQL Server

Here's what I'm looking to do in English: Select all the orders we're received (count of bookall.jobnum) since last Sunday. Also show the orders we received in the week prior (from Sunday to Saturday). Now the trick that I can't figure out is how to enter those dates automatically, meaning no dialog box where a person has to look at a calendar and enter the dates manually. Basically it's a how are we doing this week as opposed to last week type query. Any suggestions?? -- Jason Remove nospam for email replies...

  1. #1

    Default Strange date criteria.

    Here's what I'm looking to do in English:
    Select all the orders we're received (count of bookall.jobnum) since last
    Sunday. Also show the orders we received in the week prior (from Sunday to
    Saturday).
    Now the trick that I can't figure out is how to enter those dates
    automatically, meaning no dialog box where a person has to look at a
    calendar and enter the dates manually. Basically it's a how are we doing
    this week as opposed to last week type query. Any suggestions??

    --
    Jason
    Remove nospam for email replies


    Schklerg Guest

  2. #2

    Default Re: Strange date criteria.

    Thanks!!! I knew it had something to do with datepart, I just couldn't get
    there. I worship at your feet.

    --
    Jason
    Remove nospam for email replies

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:%23TkfHXXRDHA.940TK2MSFTNGP11.phx.gbl...
    > -- Assuming DATEFIRST = SUNDAY
    >
    > DECLARE weekago SMALLDATETIME, twoweeksago SMALLDATETIME
    >
    > SET weekago = DATEADD(DAY, 1-DATEPART(DW, {fn CURDATE()}), {fn
    CURDATE()})
    >
    > SET twoweeksago = weekago - 7
    >
    > -- first query (this week):
    > SELECT * FROM table WHERE dateColumn >= weekago
    >
    > -- second query (last week):
    > SELECT * FROM table WHERE dateColumn >= twoweeksago
    > AND dateColumn < weekago
    >
    >
    >
    >
    >
    > "Schklerg" <jasonbnospamintegratedbook.com> wrote in message
    > news:beeu64$4e2sn$1ID-43593.news.dfncis.de...
    > > Here's what I'm looking to do in English:
    > > Select all the orders we're received (count of bookall.jobnum) since
    last
    > > Sunday. Also show the orders we received in the week prior (from Sunday
    > to
    > > Saturday).
    > > Now the trick that I can't figure out is how to enter those dates
    > > automatically, meaning no dialog box where a person has to look at a
    > > calendar and enter the dates manually. Basically it's a how are we
    doing
    > > this week as opposed to last week type query. Any suggestions??
    > >
    > > --
    > > Jason
    > > Remove nospam for email replies
    > >
    > >
    >
    >

    Schklerg Guest

Similar Threads

  1. strange behaviour with date in SQL
    By Bjorn in forum ASP Database
    Replies: 13
    Last Post: August 3rd, 06:18 PM
  2. Strange Dec 1969 Bug with date
    By will in forum PHP Development
    Replies: 2
    Last Post: October 30th, 11:17 AM
  3. Very strange date problem
    By Willem-Jan Selen in forum ASP
    Replies: 5
    Last Post: August 12th, 12:46 PM
  4. Making a date criteria in a form
    By James Summerlin in forum Microsoft Access
    Replies: 1
    Last Post: July 11th, 01:40 PM
  5. *very* strange date issue
    By Tariq Ahmad in forum ASP.NET General
    Replies: 2
    Last Post: June 25th, 03:32 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