Solved

MSSQL Date Query Not working

Posted on 2010-11-18
8
462 Views
Last Modified: 2012-05-10
Hello

i have a MSSQL query and its returning 0 rows when i look at the data the date exists in the database.

CODE
SELECT  ID, Response, QuestionID, Dateadd, TestType, Session, WorkOrder, Comments1, Comments2

FROM    SUR_Response

WHERE  (Dateadd = CONVERT(DATETIME, '2010-11-18 00:00:00', 102))

Open in new window

0
Comment
Question by:Chris Jones
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 34164844
The date (Dateadd) is stored in the database as YY.MM.DD?

Also Dateadd is a really bad column name because it is also a function name.  You should consider changing the name of the column if it is at all practical.
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 250 total points
ID: 34164861
You are looking for that exact date. Is you are looking for everything that happended on a specific date then try.

SELECT  ID, Response, QuestionID, Dateadd, TestType, Session, WorkOrder, Comments1, Comments2
FROM    SUR_Response
WHERE  (CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,Dateadd)))) = CONVERT(DATETIME, '2010-11-18 00:00:00', 102))

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 34164871
no , the date is stored in mm/dd/yyyy but the convert changed the date format.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34164894
Also as per paulmacd he is correct that DATEADD is a function and should not be used as a column name.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 250 total points
ID: 34164924
Then the CONVERT style should be to 101, not 102:

SELECT  ID, Response, QuestionID, Dateadd, TestType, Session, WorkOrder, Comments1, Comments2
FROM    SUR_Response
WHERE  (Dateadd = CONVERT(DATETIME, '2010-11-18 00:00:00', 101))
0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 34164950
Thanks !!!!!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34164989
Try using the same convert code on both sides

Where Convert(DateTime, DateAdd, 102) = Convert(....)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34164992
Oops. Got late.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now