none
using date range in sql query

    Question

  • Hi T-SQL experts,

    I m using the below query and not i want to pull the records between the date-range:

    Date Range:

    March 11, 2010 – April 28, 2010

    Query I m using is below...

    select top 10 mes.id,u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid
    where u1.useruri like 'Donna%' or u2.useruri like 'Donna%' and mes.date in ('2010-04-28', '2011-03-11')
    order by mes.id

     

    But the problem its retuning all the dates ..:(

     

    Please help me on this...

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
    Thursday, February 02, 2012 9:32 PM

Answers

  • Try changing your where clause and adding parenthesis like:

    where ( u1.useruri like 'Donna%' or u2.useruri like 'Donna%')  and mes.date in ('2010-04-28', '2011-03-11')

    This is assuming you want data for just those 2 dates. Your sentence was confusing with "...not i want to pull the records between the date-range". Does the not mean you do not want to pull from within the range, just the 2 boudary dates.

    If you want the range use:

    where ( u1.useruri like 'Donna%' or u2.useruri like 'Donna%')  and (mes.date >= '2010-04-28' and mes.date <= '2011-03-11')


    Dave Frommer - BI Architect - Independent
    Thursday, February 02, 2012 9:43 PM
  • You need parenthesis around the OR clauses, and you check for mes.date will only return rows with dates of March 11 and April 28.  And you heading says 2010 dates, but your query references a 2011 date.  If you want all dates on or between Mar 11, 2010 and Apr 28, 2010 then

     

    select top 10 mes.id,u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid
    where (u1.useruri like 'Donna%' or u2.useruri like 'Donna%') 
    and mes.date >= '20100311' and mes.date <  '20100429'
    order by mes.id
    
    Tom

     

    Thursday, February 02, 2012 9:45 PM
  • Try:

     

    select top 10 mes.id,u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid 
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid 
    where (u1.useruri like 'Donna%' or u2.useruri like 'Donna%') and mes.date between '20100428' and'20110311'
    order by mes.id
    

    Assuming you wanted a range, not just 2 dates.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 02, 2012 9:51 PM

All replies

  • Try changing your where clause and adding parenthesis like:

    where ( u1.useruri like 'Donna%' or u2.useruri like 'Donna%')  and mes.date in ('2010-04-28', '2011-03-11')

    This is assuming you want data for just those 2 dates. Your sentence was confusing with "...not i want to pull the records between the date-range". Does the not mean you do not want to pull from within the range, just the 2 boudary dates.

    If you want the range use:

    where ( u1.useruri like 'Donna%' or u2.useruri like 'Donna%')  and (mes.date >= '2010-04-28' and mes.date <= '2011-03-11')


    Dave Frommer - BI Architect - Independent
    Thursday, February 02, 2012 9:43 PM
  • You need parenthesis around the OR clauses, and you check for mes.date will only return rows with dates of March 11 and April 28.  And you heading says 2010 dates, but your query references a 2011 date.  If you want all dates on or between Mar 11, 2010 and Apr 28, 2010 then

     

    select top 10 mes.id,u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid
    where (u1.useruri like 'Donna%' or u2.useruri like 'Donna%') 
    and mes.date >= '20100311' and mes.date <  '20100429'
    order by mes.id
    
    Tom

     

    Thursday, February 02, 2012 9:45 PM
  • Try:

     

    select top 10 mes.id,u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid 
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid 
    where (u1.useruri like 'Donna%' or u2.useruri like 'Donna%') and mes.date between '20100428' and'20110311'
    order by mes.id
    

    Assuming you wanted a range, not just 2 dates.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 02, 2012 9:51 PM
  • Hi All,

    Date Range:

    March 11, 2010 – April 28, 2010

      

    I want all the data between in the above date range.

     

    Please let me know how can use the query:

    select top 10 u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes with (nolock)
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid
    where u1.userid = 116 or u2.userid = 116
    order by mes.id

     

    Thanks, 


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
    Thursday, February 02, 2012 10:00 PM
  • Try then:

    select top 10 u1.useruri as [FROM],u2.useruri as [TO],mes.body,mes.date from [messages] as mes 
    inner join LCSLog.dbo.users as u1 on u1.userid = mes.fromid 
    inner join LCSLog.dbo.users as u2 on mes.toid = u2.userid 
    where (u1.userid = 116 or u2.userid = 116)
    and me.[date] >='20100311' and mes.date < '20100429'
    order by mes.id
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, February 02, 2012 10:14 PM