Solved

having problem using datadiff, getting conversion failed when converting date...from character string

Posted on 2010-09-20
10
234 Views
Last Modified: 2012-05-10
I have a couple of date fields. If I just do select date1, date2 from tbl. They look the same (like regular date/time fields). If I do
select DateDiff(dd, date1, GetDate())
works fine
this one gets the error
select DateDiff(dd, date2, GetDate())
this also gets the same error
select DateDiff(dd, cast(date2 as datetime), GetDate())

So, I'm thinking date2 is a string, but, why does it look like a date if selected alone and how do I tell what data type it is?

How do I get it to work with the datediff?

thanks
0
Comment
Question by:avoorheis
  • 4
  • 3
  • 3
10 Comments
 
LVL 11

Accepted Solution

by:
aelliso3 earned 300 total points
Comment Utility
Try this:

select DateDiff(dd, convert(datetime,(date2 )), GetDate())
0
 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 200 total points
Comment Utility
Maybe remove the datetime like:

select DateDiff(dd, cast(date2 as datetime), GetDate())
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
Just for future knowledge, you can use the following to find the datatypes and some other info about the tables as well:
 

SELECT TABLE_CATALOG AS DBName

     , TABLE_SCHEMA AS SchemaName

     , COLUMN_NAME AS ColumnName

     , DATA_TYPE AS DataType

     , CHARACTER_MAXIMUM_LENGTH AS MaxLength

     --, *

FROM information_schema.columns

WHERE table_schema = 'dbo'

     AND table_name = 'TableName'

     --AND column_name = 'col'

Open in new window

0
 

Author Comment

by:avoorheis
Comment Utility
DateDiff(dd, convert(datetime, date2), GetDate())       does not work, same error

select DateDiff(dd, cast(date2 as datetime), GetDate())     does not work, same error


0
 

Author Comment

by:avoorheis
Comment Utility
date1 and date2 are both date time fields
I must be missing something, but, I guess that's the problem
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:avoorheis
Comment Utility
hold on...it works ok directly from the original table, the query I'm using has lots of other things going on....let me review a bit more and contine in a little bit
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
>> select DateDiff(dd, cast(date2 as datetime), GetDate())     does not work, same error
are you sure?

Be sure you are selecting from a table

 select DateDiff(dd, cast(date2 as datetime), GetDate())    FROM...

BTW, what is the date2 data type?

It worked for me when I just tried it now.
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
sammySeltzer ... read the comment directly above you're last post ...
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
yea, I posted before I saw that comment, sorry.
0
 

Author Comment

by:avoorheis
Comment Utility
the problem was in a different area
thanks for looking and posting
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now