Solved

MSSQL Date Query Not working

Posted on 2010-11-18
8
471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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