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


When running the script below in a third-party product that integrates with SQL, we get the following error message:

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

Any ideas as to why?



/*create Service Channel Payment File*/
use [master]
if object_id('tempdb.dbo.#database') is not null
    drop TABLE #database
create TABLE #database(id INT identity primary key, name sysname)
if object_id('tempdb.dbo.#Payments') is not null
    drop TABLE #Payments

create TABLE #Payments(ID INT identity primary key, VendorID char(15), InvNum char(21),  PayDate datetime, PymntAmt numeric(18,4), PtmntNum char(21), VENDORCLSID char(15), COID char(5))
set nocount on

insert into #database(name)

select name
from sys.databases
where name like  '%ZK%' or name like 'SF' or name like 'ZOEPA'

and source_database_id is null

order by name

--Select *
--from #database

declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname;

select @id = 1, @cnt = max(id)
from #database

while @id <= @cnt


    select @currentDb = name
    from #database
    where id = @id

    set @sql = 'select pm.VENDORID, pm.APTODCNM, cast(pm.DOCDATE as datetime), pm.APPLDAMT, pm.APFRDCNM, v.VNDCLSID  from '+ @currentDb + '.dbo.PM30300 pm join '+ @currentDb + '.dbo.PM00200 v on v.VENDORID = pm.VENDORID'

    --exec (@sql);
    insert into #Payments(VendorID,InvNum,PayDate,PymntAmt,PtmntNum,VENDORCLSID)
                update #Payments set COID = @currentDb where COID is null
    set @id = @id + 1;


select  * from #Payments
and [PayDATE] between convert(datetime,'GBL_DOCUMENTSTARTDATE',120) and convert(datetime, 'GBL_DOCUMENTENDDATE',120)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
character field --> datetime field blew up

CAST(pm.DOCDATE as datetime) --> #Payments.PayDate

So for starters, what's the data type for pm.DOCDATE?  If it's a character field, any value that cannot be converted to a valid datetime will cause this error.

So, give this a whirl, and if there are any rows returned, you'll need to fix/handle them..

FROM whatever_pm_is

Open in new window

TBSupportAuthor Commented:
Thanks for the quick response, Jim!

pm comes from a table called PM30300.  The DOCDATE field is in that table is a datetime field.

When I run your script, no values are returned.

We changed CAST(pm.DOCDATE as datetime) to pm.DOCDATE, but we still got the same error.

Jim HornSQL Server Data DudeCommented:
>and [PayDATE] between convert(datetime,'GBL_DOCUMENTSTARTDATE',120) and convert(datetime, 'GBL_DOCUMENTENDDATE',120)
This might be it.  Lose the quote marks around the GBL_ columns, as it'll treat that as the string GBL.. instead of the column with that name.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

TBSupportAuthor Commented:
Hi Jim:

I'm afraid that that did not work either.  Any other suggestions?

Thanks, again!

TBSupportAuthor Commented:
Hi Jim:

Long story short, we did determine as you did that there is something "wrong" with the following:

and [PayDATE] between convert(datetime,'GBL_DOCUMENTSTARTDATE',120) and convert(datetime, 'GBL_DOCUMENTENDDATE',120)

Specifically, the conversion syntax is not working.  Can you think of another "form" of conversion syntax that can be used, instead?


Doug BishopDatabase DeveloperCommented:
First if DOCDATE is a DATETIME data type, as mention above, you do not need "cast(pm.DOCDATE as datetime)"; just use pm.DOCDATE. Why cast a datetime datatype to a datetime datatype?

Where are GBL_DOCUMENTSTARTDATE and GBL_DOCUMENTENDDATE coming from? They do not exist in the #payments table. What are some sample values? They may be your culprit. I prefer CAST instead of CONVERT because it is ANSI compliant. Use CONVERT when you need to convert a date to a string for display purposes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
TBSupport, do you still need help with this question?
Jim HornSQL Server Data DudeCommented:
Kinda hard to continue to troubleshoot this question since we don't have access to your data source(s) and can't run SQL on our own.  If there are column naming issues, we have no way of knowing.

So right now we're at..
>and [PayDATE] between convert(datetime,'GBL_DOCUMENTSTARTDATE',120) and convert(datetime, 'GBL_DOCUMENTENDDATE',120)
Lose the single quote marks
Are these column names not correct?
If these are dates, why convert in the first place?
Is PayDATE a date?  If not, comparing a varchar to BETWEEN dates will throw an error.
I dunno.  Gremlins?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.