Solved

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

Posted on 2010-09-20
10
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 11

Accepted Solution

by:
aelliso3 earned 300 total points
ID: 33719304
Try this:

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

Assisted Solution

by:sammySeltzer
sammySeltzer earned 200 total points
ID: 33719306
Maybe remove the datetime like:

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

Expert Comment

by:aelliso3
ID: 33719370
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:avoorheis
ID: 33719445
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
ID: 33719464
date1 and date2 are both date time fields
I must be missing something, but, I guess that's the problem
0
 

Author Comment

by:avoorheis
ID: 33719524
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 29

Expert Comment

by:sammySeltzer
ID: 33719566
>> 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
ID: 33719638
sammySeltzer ... read the comment directly above you're last post ...
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33719707
yea, I posted before I saw that comment, sorry.
0
 

Author Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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