Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Thread: Query failis

  1. #1
    Join Date
    Dec 2003
    Posts
    19

    Red face Unanswered: Query failis

    HAI Friends
    I need some info.
    I have written a query which was working fine with DB user id and password deticated to this DB which i am using.
    Then we change the login mode to Windows Login with Trusted connection. After this change my query is failing.
    My query is which working in SQL login mode is
    SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between '29/12/2003 00:00:01' and '30/12/2003 23:59:59' Order by DATE_TIME ASC

    Where DATE_TIME is date time Data type.

    i change this query to check with Windows login with trusted connection.
    SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 ) Order by DATE_TIME ASC
    No Error is mention for this query but no records are fetched. But data is there in DB.
    Please help i am new to SQL server.
    Expecting your replies ASAP
    Signing Off
    Venkatesh.V

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Has the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?

  3. #3
    Join Date
    Dec 2003
    Posts
    19
    Originally posted by rnealejr
    Has the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?
    Yes i have access to Tables. i have been given DB owner rights.

    i have to run these queries form Visual basic. i have to populate these data as reports using Crystal reports. Query is not working even in Query Analyser.
    i ma trying it out with 2 diff login id with query analyser

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You connected to the server?

    Then you're in....

    And I bet you're trying to execute the code in master...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    But he would get invalid object name if that were the case ...

  7. #7
    Join Date
    Dec 2003
    Posts
    19
    Originally posted by rnealejr
    In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?
    Dear Friend,
    I ma able to see data if i put a Select * Statement and i ma able to get count etc..
    Only thing this query fails ..

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    It shoulds like your where clause is hosed - leave off the where clause and try again. If that returns rows, what values are stored in the date_time field ?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First:

    You need to post the error message. We're just guess right now as to what the problem is.

    Second:

    You need to post the Query and The DDL of the Tables

    Third:

    Sample Data would be a BIG help
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2003
    Posts
    19
    Originally posted by Brett Kaiser
    First:

    You need to post the error message. We're just guess right now as to what the problem is.

    Second:

    You need to post the Query and The DDL of the Tables

    Third:

    Sample Data would be a BIG help
    Dear Frnd,
    I have ran the query with out any condtion. it works. Even with one condtion by checking only first date value it works. when we put the between caluse in the query it returns nothing.

    i have attched the table strucure,index,constraints and sample data. please have look into this and help me out.
    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

    between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

    is NOT the same as

    between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

    At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

    Why did you switch to using a convert function?

    blindman

  12. #12
    Join Date
    Dec 2003
    Posts
    19
    Originally posted by blindman
    You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

    between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

    is NOT the same as

    between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

    At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

    Why did you switch to using a convert function?

    blindman
    As my Previous Query was not working under Trusted conenction it throws me error stating that "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
    So to solve this i tired with Convert fucntion. Still after that no records are fetched. But data is there in Table.

  13. #13
    Join Date
    Feb 2002
    Posts
    2,232
    If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?

  14. #14
    Join Date
    Dec 2003
    Posts
    19
    Originally posted by rnealejr
    If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?
    Datatype for DATE_TME is datetime.
    when we select date_time alone we get data as
    30/12/2003 11:01:10 μμ
    30/12/2003 10:34:18 μμ
    30/12/2003 10:34:01 μμ

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I looked at your attachment...and it does look like the column is defined as datetime...

    Your data however doesn't....

    Do this and tell us what you get

    [grasping at straws now]

    SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0

    If you get anything other than 0, you'll have non date data...

    Which is what your error message is telling you...but not what your table def shows

    [/grasping at straws now]

    Also try DBCC CHECKTABLE ('TBL_INFO')

    And let use know what that says....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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