Conversion failed when converting date and/or time from character string.

Question

1.00/5 (1 vote)

See more:

ASP.NET

select DATEDIFF(day,allocate_date,deallocate_date) as dDiff from ward

help me to convert date/time from string

Posted 7-Apr-14 3:41am

Member 10694442

Add a Solution

3 solutions

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)

Suk@nta · Answer 1 · 2014-04-07T03:58:00

Solution 1

SQL

try below one

select DATEDIFF(day,CONVERT(DATETIME,allocate_date),CONVERT(DATETIME, deallocate_date)) as dDiff from ward

let me know it help you or not

Posted 7-Apr-14 3:58am

Suk@nta

Comments

Member 10694442 7-Apr-14 10:04am

no its not working

Suk@nta 7-Apr-14 10:20am

what is the data type for allocate_date and deallocate_date and which database you are using

Member 10694442 7-Apr-14 10:25am

varchar

Member 10694442 7-Apr-14 10:30am

sql database

Suk@nta 7-Apr-14 10:40am

means sql server or mysql

Suk@nta 7-Apr-14 10:53am

i tested below code it will work

if the date in dd/mm/yyyy format then try below one
select DATEDIFF(day,CONVERT(DATETIME,allocate_date,103),CONVERT(DATETIME, deallocate_date,103)) as dDiff

if in mm/dd/yyyy
select DATEDIFF(day,CONVERT(DATETIME,allocate_date,101),CONVERT(DATETIME, deallocate_date,101)) as dDiff

//if it solve your problem plz don't forgot to accept the ans and give 5 star

vsrikanth87 · Answer 2 · 2014-04-07T06:49:00

Solution 2

Refer

http://technet.microsoft.com/en-us/library/ms189794.aspx[^]

http://www.1keydata.com/sql/sql-datediff.html[^]

Posted 7-Apr-14 6:49am

vsrikanth87

Kan07 · Answer 3 · 2014-04-07T23:15:00

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate
this is format ..........I think your allocate_date,deallocate_date are not in date format ...convert it to date

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(24),GETDATE(),113)
The result would look something like this:

Nov 04 2011 11:45 PM
11-04-11
04 Nov 2011 11:45:34:243