We help IT Professionals succeed at work.

SQL Query not working properly

256 Views
Last Modified: 2013-12-17
I am trying to create a stored procedure that takes variables and returns the data to me.


SELECT sysdba.ACCOUNT.ACCOUNT, sysdba.HISTORY.USERNAME,
               sysdba.HISTORY.LONGNOTES, sysdba.HISTORY.COMPLETEDDATE,  
               sysdba.HISTORY.MODIFYDATE
FROM  sysdba.ACCOUNT INNER JOIN
               sysdba.HISTORY ON sysdba.ACCOUNT.ACCOUNTID = sysdba.HISTORY.ACCOUNTID
WHERE (sysdba.ACCOUNT.ACCOUNT = @AccountName) AND (sysdba.HISTORY.LONGNOTES IS NOT NULL) AND (sysdba.HISTORY.COMPLETEDDATE = @CompDate)


sysdba.HISTORY.COMPLETEDDATE is stored in the database like so:  2007-07-19 17:48:30.000
if i try to use 7-19-2007 as the date i am searching for, is doesnt find anything, and I cant figure out how I would modify my sql so it would find the record.

Help!

Thank you,

Nurve Tech
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
if you need to find the records belongs to that particular day

>AND (sysdba.HISTORY.COMPLETEDDATE = @CompDate)
change the above to

AND sysdba.HISTORY.COMPLETEDDATE  >=@CompDate and sysdba.HISTORY.COMPLETEDDATE  < @CompDate + 1

Author

Commented:
Thats not my Issue at all, my issue is that the date is stored in the db as

2007-07-19 17:48:30.000

my variable @CompDate is entered by the user in my application and returned as:

07-09-2007 5:48 PM

When the search is done, it returns nothing because the dates dont match up, how would i fix this?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
the datetime datatype stores both date and time, now if you pass the value "07-09-2007 5:48 PM" , sql will be confused whether it is sept 07 or July 07, so the best option is to pass it as YYYY-mm-dd format
SELECT sysdba.ACCOUNT.ACCOUNT, sysdba.HISTORY.USERNAME,
               sysdba.HISTORY.LONGNOTES, sysdba.HISTORY.COMPLETEDDATE,  
               sysdba.HISTORY.MODIFYDATE
FROM  sysdba.ACCOUNT INNER JOIN
               sysdba.HISTORY ON sysdba.ACCOUNT.ACCOUNTID = sysdba.HISTORY.ACCOUNTID
WHERE (sysdba.ACCOUNT.ACCOUNT = @AccountName) AND (sysdba.HISTORY.LONGNOTES IS NOT NULL) AND (convert(varchar, sysdba.HISTORY.COMPLETEDDATE, 101) = @CompDate)
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
@sql_server_dba

the 'convert(varchar, sysdba.HISTORY.COMPLETEDDATE, 101)' will not make use of any indexes defined on that CompletedDate Column.
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2007

Commented:
... and aneeshattingal is right when saying that SQL Server will get confused when entering 07-09-2007.
It will make up the date based on the windows settings and has to choose between Sept 7th or July 9th.

Don't let Windows make that choice for you and enter it in the - for SQL Server - clear format 20070907. SQL Server will interpret this as Sept 7th 2007 ... every time, no matter what.

Hope this helps ...
Top Expert 2007

Commented:
Glad I could be of any help and thanks for the grade !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.