SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot GWild's Avatar
    Join Date
    May 2005
    Location
    Colorado
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Query Across Year Fails

    Hello.

    Platform: MSSQL Server 2005.

    I'm accustomed to MySQL and Oracle, and I have encountered an issue which seems trivial in nature, but I cannot seem to overcome it.

    I have a database table containing a column of datetime type. I need to query against this column where the date range crosses over into the next year. Something along the lines of:
    Code:
    PSEUDO:
    WHERE
        somefield.date BETWEEN '12/01/10' AND '01/03/11'
    I have been trying variants of:
    Code:
    WHERE
        table.run_date BETWEEN CONVERT(CHAR(8), '12/01/10', 1) AND DATEADD(Day, +35, CONVERT(CHAR(8), '12/01/10', 1))
    
    and
    
    WHERE
    CONVERT(CHAR(10), table.run_date, 101) BETWEEN CONVERT(CHAR(10), '12/01/2010', 101) AND CONVERT(CHAR(10), '01/03/2011', 101)
    but the query will not return any data for dates later than 12/31/10 (and data does in fact exist for dates later than 12/31/10). Some variants return no data at all.

    Frankly I'm mystified that a major database engine does not support something as simple as the pseudo I posted above while it is so straight forward in Oracle, MySQL..... (But that is another issue).

    Any assistance/guidance as to how to accomplish this task is appreciated.
    Lang ist der weg und hart,
    das aus der hoelle fuehrt auf zum licht.


    My Site

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    WHERE somefield.date BETWEEN '2010-12-01' AND '2011-01-03'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot GWild's Avatar
    Join Date
    May 2005
    Location
    Colorado
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, I'll give that a shot.
    Lang ist der weg und hart,
    das aus der hoelle fuehrt auf zum licht.


    My Site

  4. #4
    SitePoint Zealot GWild's Avatar
    Join Date
    May 2005
    Location
    Colorado
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,

    Your suggestion, of course, worked like a charm.

    A thick head with Rockwell hardness off the scale is not a good thing. Must work to abandon both.

    Someday I'll learn that it's rarely a Zebra - and usually a Horse....

    Thank you.
    Lang ist der weg und hart,
    das aus der hoelle fuehrt auf zum licht.


    My Site


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
  •