Conversion failed when converting from character to date or time

Question

Post reply

Conversion failed when converting from character to date or time

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

declare @todate as date = '01/04/2012'

Is this meant to be April 1st or January 4th?

Why do you prepare two date variables, then use literals in your query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aar.mba Old Hand Points: 378 More actions · Answer 2

Actually I want the user to give values in runtime. Those two(@fromdate and @todate) are parameters.

For testing purpose I gave date ranges in literals.

its Jan 04 2012.MM/DD/YYYY format

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

aar.mba (4/11/2014)
Actually I want the user to give values in runtime. Those two(@fromdate and @todate) are parameters.
For testing purpose I gave date ranges in literals.
its Jan 04 2012.MM/DD/YYYY format

What do you expect po_date (VARCHAR(10) or whatever) to implicitly convert to? What might cause the conversion to fail?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aar.mba Old Hand Points: 378 More actions · Answer 4

Po_date to be converted to date format..

so that if the user enters the date in report then appropriate data should be fetched.

the date parameter in SSRS should be mapped to the column of same data type.

the default format for date parameter in ssrs is "mm/dd/yyyy".

Since the query works fine with "Late" status,i think the problem is with Po_date value corresponding to

"ontime" status

Is there any other way?

aar.mba Old Hand Points: 378 More actions · Answer 5

Also I ran the below query

select isdate(po_date)

from #mytable

where status = 'ontime'

select isdate(po_date)

from #mytable

where status = 'late'

for status "ontime" some of the rows returned 0

for status "late" all the rows returned 1

So the row which returned 0 has problem with the data of Po_date?

Pls note that ran all the queries what you sent in my UAT

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Better to use an explicit conversion of string-date to actual date, than hope that local settings will accidentally give you the result you want with an implicit conversion.

CONVERT(DATE,po_date,101)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden