?
Solved

SQL Query not working properly

Posted on 2007-10-10
8
Medium Priority
?
222 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
0
Comment
Question by:NurveTech
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20050572
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
0
 

Author Comment

by:NurveTech
ID: 20050600
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?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20050645
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20050994
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)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20051025
@sql_server_dba

the 'convert(varchar, sysdba.HISTORY.COMPLETEDDATE, 101)' will not make use of any indexes defined on that CompletedDate Column.
0
 
LVL 18

Accepted Solution

by:
Yveau earned 2000 total points
ID: 20052443
First, I think this: 07-09-2007 5:48 PM is a typo for 07-19-2007 5:48 PM

Second, the date is actually not stored as '2007-07-19 17:48:30.000' at all, but as 39280.64110000 ... :-)
What you are seeing is only a formatted representation of the date stored in the database as a double 4 byte integer, based on the local settings of your server.

If you want to find the record with the value  2007-07-19 17:48:30.000 you should look for
@CompDate = '20070719 17:48:30' and not '07-19-2007 5:48' which equals to '20070719 17:48:00.000' and that is not what is entered in the database !

If you want to forget all about the seconds, use this:
    sysdba.HISTORY.COMPLETEDDATE between @CompDate and dateadd(mi, 1, @CompDate)
instead of just:
    sysdba.HISTORY.COMPLETEDDATE = @CompDate

If you want to find the exact record, enter @CompDate as 07-19-2007 5:48:30.000 PM


Hope this helps ...

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20052472
... 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 ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20196959
Glad I could be of any help and thanks for the grade !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question