SQL server query error

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

AM getting this error while performing this query operation

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

here' s my query

SQL

SELECT 
 MaxDate,
 MinDate,
DATEDIFF(D, MaxDate, MinDate)
FROM (
SELECT
     (SELECT MAX(TDate) ) AS MaxDate
    ,(SELECT MIN(TDate) ) AS MinDate
FROM EDATA
)a

Posted 28-Aug-14 3:41am

Abhishek Jaiswall

Updated 28-Aug-14 4:05am

Mike Meinz

v2

Add a Solution

Comments

[no name] 28-Aug-14 9:46am

Store date/time as datetimes not as strings.

Abhishek Jaiswall 28-Aug-14 9:55am

I didn't get you!

PhilLenoir 28-Aug-14 9:47am

What is your data type of TDate and do you have an example of a value causing the error?

Abhishek Jaiswall 28-Aug-14 9:54am

My datatype is date and dates are stored like this '3/3/12' ..

PhilLenoir 28-Aug-14 10:02am

Where is the query being run? The stucture of your query is a little odd, but it works (although you will get a negative number from your DATEDIFF!) Is EDATA a table or is it a SQL View? Somehow your query processor is treating TDATE as a string.

2 solutions

Solution 1

try this

SQL

SELECT MAX(TDate)  AS MaxDate, MIN(TDate) AS MinDate , 
Datediff(d, MAX(TDate), Min(TDate)) FROM EDATA

or

SQL

SELECT
MaxDate,
MinDate,
DATEDIFF(D, MaxDate, MinDate)
FROM (
SELECT MAX(TDate)  AS MaxDate, MIN(TDate) AS MinDate FROM EDATA
)a

Posted 28-Aug-14 4:00am

member33

Comments

Abhishek Jaiswall 29-Aug-14 0:02am

Still getting same error through this but, no worries. I have solved it my own. Thnks for your kind reply. #CHeers!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhishek Jaiswall · Accepted Answer · 2014-08-28T18:06:00

What i did is-
1: Changed my table creation query and used DATETIME in place of DATE

CREATE TABLE EDATA
(
ID INT not null,
Name NVARCHAR(50)not null,
TDate DATETIME not null,
TCard INT not null
);

2:
and then simply query is working without using CAST function, as

SQL

SELECT
MaxDate,
MinDate,
DATEDIFF(D, MaxDate, MinDate)
FROM (
SELECT
   MAX(TDate) AS MaxDate,
   MIN(TDate) AS MinDate
   FROM EDATA
   GROUP BY TCard
)a