Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL Date Query Not working

Posted on 2010-11-18
8
Medium Priority
?
484 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 1000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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