Solved

MSSQL Date Query Not working

Posted on 2010-11-18
8
477 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 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