Professional Web Applications Themes

Ignoring 'Having' clause - Microsoft SQL / MS SQL Server

Hi, Taking this query: select DepotOutletCodeId, min(SaleDate) as FirstSaleDate from tblCases group by DepotOutletCodeId Having Min(SaleDate) >= '01/07/2002' Why am I having dates returned in my query before '01/07/2002' ? I thought a having clause was like a 'where' clause for group by. Or am I confused here? p.s Are you guys going to need some table definitions to help you? Just let me know and I'll try and sort one out. Thanks...

  1. #1

    Default Ignoring 'Having' clause

    Hi,

    Taking this query:

    select
    DepotOutletCodeId,
    min(SaleDate) as FirstSaleDate
    from
    tblCases
    group by
    DepotOutletCodeId
    Having
    Min(SaleDate) >= '01/07/2002'

    Why am I having dates returned in my query before '01/07/2002' ?
    I thought a having clause was like a 'where' clause for group by. Or am I
    confused here?

    p.s Are you guys going to need some table definitions to help you? Just let
    me know and I'll try and sort one out.

    Thanks


    London Guest

  2. #2

    Default Re: Ignoring 'Having' clause

    Argh! Spot on.
    I was looking for a really complex problem and it was nice and simple.
    Thanks

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... [/ref]

    > let 
    >
    >[/ref]


    London Guest

  3. #3

    Default Re: Ignoring 'Having' clause

    I will add also to do an explicit cast on the date no matter how submitted
    to ensure types are equal.

    select
    DepotOutletCodeId,
    min(SaleDate) as FirstSaleDate
    from
    tblCases
    group by
    DepotOutletCodeId
    Having
    Min(SaleDate) >= cast('01/07/2002' as datetime)

    depending on your SQL version the ISO date will still be implicitly
    converted being explicit ensure you get the typing you expect.

    Take you previous date it consideration if the date value it will implicit
    all data as a char type and do an alphabetic sort

    so

    01/08/2000

    because 8 is greater than 7 in alpha sort.

    By be explicit it will not make assumptions on the data and will ensure
    proper sort order for comparison.

    "London Developer" <com> wrote in message
    news:phx.gbl... [/ref]
    7 [/ref][/ref]
    am [/ref][/ref]
    Just 
    > >
    > >[/ref]
    >
    >[/ref]


    James Guest

  4. #4

    Default Re: Ignoring 'Having' clause

    Using CAST on a character variable to change it to datetime doesn't solve
    the problem. Try the following:
    SET DATEFORMAT mdy
    SELECT CAST('2/28/2003' AS DATETIME)
    SET DATEFORMAT dmy
    SELECT CAST('2/28/2003' AS DATETIME)

    That's why putting all your dates in YYYYMMDD is so useful, because then you
    don't have to worry about the dateformat.

    If you want to explicitly convert your dates use CONVERT(DATETIME,
    '2/28/2003', 101), but that means that if you work in a multinational
    environment you have to change your code for different locations. :-(
    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "James Travis" <com_antispam> wrote in message
    news:g1kRa.300737$southeast.rr.com... [/ref][/ref]
    as [/ref][/ref]
    that [/ref]
    > am [/ref]
    > Just 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. ignoring HTML with a replaceNoCase
    By gobbawee in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 23rd, 05:51 AM
  2. Ignoring in a list ?
    By craig_uk in forum Macromedia ColdFusion
    Replies: 7
    Last Post: April 15th, 08:51 AM
  3. Ignoring a line in a text file?
    By craig_uk in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 22nd, 12:43 PM
  4. Replies: 1
    Last Post: September 9th, 07:09 PM
  5. postfix: ignoring certain uids?
    By Georg Schwarz in forum Linux / Unix Administration
    Replies: 0
    Last Post: July 6th, 02:57 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