none
SQL Query modified to include a date range instead of a single date.

    Question

  • How should this be written to include the date range 2013-08-01 to 2013-09-06?

    This is a SQL query to access chat logs in MS OCS.

    SELECT date, u1.useruri as [from], body, u2.useruri as [to] from messages, users u1, users u2 
    where (messages.fromid = u1.userid and messages.toid = u2.userid)
    and CONVERT(CHAR(10),date,120) = '2013-08-01'

    order by date

    Monday, September 09, 2013 4:38 PM

Answers

  • Hi

    Why are you converting [date] column to varchar..

    Please try the code bellow

      SELECT date, u1.useruri as [from], body, u2.useruri as [to] from messages, users u1, users u2 
    where (messages.fromid = u1.userid and messages.toid = u2.userid)
    and [date]>= '2013-08-01' AND [date]<'2013-09-06'

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 09, 2013 4:41 PM

All replies

  • Hi

    Why are you converting [date] column to varchar..

    Please try the code bellow

      SELECT date, u1.useruri as [from], body, u2.useruri as [to] from messages, users u1, users u2 
    where (messages.fromid = u1.userid and messages.toid = u2.userid)
    and [date]>= '2013-08-01' AND [date]<'2013-09-06'

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 09, 2013 4:41 PM
  • Sweet! Thanks. One more thing. How about if I wanted to see all chats from a single user to anyone else from the same date range?
    Monday, September 09, 2013 4:44 PM
  • So essentially I have 5 users I need to see chat history on from 08-01-2013 to 09-01-2013
    Monday, September 09, 2013 4:49 PM
  • Try

    SELECT m.date, u1.useruri as [from], m.body, u2.useruri as [to]

    FROM messages M INNER JOIN users u1 ON M.fromid = u1.userid

    INNER JOIN users u2 ON M.toid = u2.userid WHERE m.[date]>= '20130801' AND m.[date]<'20130906' AND m.fromID = @UserId



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


    My blog


    My TechNet articles

    Monday, September 09, 2013 4:51 PM
    Moderator
  • It generated an error indicating "Could not add the table 'j'.
    Monday, September 09, 2013 5:21 PM
  • Can you post your exact query and also what tool you're using for writing and testing queries?

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


    My blog


    My TechNet articles

    Monday, September 09, 2013 5:28 PM
    Moderator
  • Sure. I'm using Microsoft Query SP2 then adding an SQL statement including these commands. 
    Monday, September 09, 2013 6:18 PM
  • I am not familiar with Microsoft Query SP2 but if you used the query as I posted, try using a constant (for now) instead of @UserId. In my case I assumed you will be providing @UserId as a parameter. Try using actual userId (number, I assume).

    The query itself I posted is correct and I hope that MS Query SP2 should recognize it OK.

    Also, will you be able to try it in SSMS (SQL Server Management Studio)?


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


    My blog


    My TechNet articles

    Monday, September 09, 2013 6:43 PM
    Moderator