Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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

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
avoorheis
Asked:
avoorheis
  • 4
  • 3
  • 3
2 Solutions
 
aelliso3Commented:
Try this:

select DateDiff(dd, convert(datetime,(date2 )), GetDate())
0
 
sammySeltzerCommented:
Maybe remove the datetime like:

select DateDiff(dd, cast(date2 as datetime), GetDate())
0
 
aelliso3Commented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
avoorheisAuthor Commented:
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
 
avoorheisAuthor Commented:
date1 and date2 are both date time fields
I must be missing something, but, I guess that's the problem
0
 
avoorheisAuthor Commented:
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
 
sammySeltzerCommented:
>> 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
 
aelliso3Commented:
sammySeltzer ... read the comment directly above you're last post ...
0
 
sammySeltzerCommented:
yea, I posted before I saw that comment, sorry.
0
 
avoorheisAuthor Commented:
the problem was in a different area
thanks for looking and posting
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now