Solved

MSSQL Date Query Not working

Posted on 2010-11-18
8
467 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 33
fault SQL backup files that wont restore - how common 3 25
SQL Recursion 6 19
How can I do Unit Testing with Session ? 1 11
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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