Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Unanswered: SQL Date Range Query

    Hi Folks

    Need a little help with a Date Range query;

    Im using SQL 2000 and the field is set to datetime

    I need to query from now to 30 days ago, its very basic I know...

    The field is called - ArtDate

    Any help appreciated !

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
     ... where ArtDate >= dateadd(d,datediff(d,0,getdate()),-30)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Thanks

    for that, was having a moment....

    I have one more if you can help please...

    Date query again - need to query between two dates, but the year part is to be ignored.

    Need to query between 20 March and 20 June - the year must not be queried..

    Many thanks

  4. #4
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    What i have so far....

    SELECT Title, ArtDate
    FROM dbo.tbl_alerts
    WHERE (MONTH(ArtDate) BETWEEN '06' AND '09')

    This will do the month query, but I need to specify start and end day as well as the month.

    The year is to be ignored.

    ANy help appreciated

    Donald

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is tougher than it looks. Do you want to be able to query between, say, November and February, where your range overlaps two years?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113
    Hi - i need to setup 4 queries to perford the following;

    20 March to 20 June
    21 June to 22 September
    23 September to 21 December
    22 December 19 March

    Each must only query the above date range BUT NOT the year.

    I have a DTS running that deletes all records that are older than 365 days.

    Hope that helps ?

    Donald

  7. #7
    Join Date
    Jul 2006
    Posts
    22
    maybe something like this ?

    (for first case)
    Code:
    ... where (month(ArtDate)=03 and day(ArtDate)>=20 )
    or month(ArtDate) in (4,5)
    or (month(ArtDate)=06 and day(ArtDate)<=20 )
    For other ranges should change the respective values of course.
    Last edited by carloco; 08-01-06 at 13:47.

  8. #8
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Thanks

    Thats perfect !

    Many thanks for helping

Posting Permissions

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