Conversion failed when converting from character to date or time

Question

Conversion failed when converting from character to date or time

aar.mba

Old Hand

Points: 378
More actions
April 11, 2014 at 4:01 am

#292263

Hi,
I'm getting conversion failed error while converting nvarchar to date/time.
I tried everything that has been given in forums.
SELECT LOCATION,SUM(SHIPQTY) LATE_QTY ,PO_DATE FROM mytable
AND SUBSTRING(CAST(PO_DATE AS DATE),1,2) + '/' + SUBSTRING(CAST(PO_DATE AS DATE),4,2)+'/'+
SUBSTRING(CAST(PO_DATE AS DATE) ,7,4) BETWEEN @Fromdate AND @Todate
I used convert,cast and cast as varchar and then to date.
But nothing works.
because of this problem the data is not properly filtered in report.if I filter for jan 2013 it gives me jan 2012 data
since I don't have data for jan 2013.But actually it should not return any value for jan 2013.
the date column in my table is in nvarchar format.
Pls advise.I spent almost a week for this.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

How is the date formatted as varchar? Is it in words or 'yyyy-mm-dd', 'dd-mm-yyyy' or 'mm-dd-yyyy' or some variation of that? If you could post some sample data it would make it much easier to help.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

SQLCJ SSCommitted Points: 1791 More actions · Answer 2

Did you go through following link?

https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

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

The data looks like this.mm/dd/yyyy format but data type is nvarchar for this column

02/24/2012

01/20/2012

01/27/2012

Neil Burton SSC-Insane Points: 23000 More actions · Answer 4

I'm guessing a bit here but is this something like what you're after?

create table #mytable

(

Locationvarchar(20)

,shipqtyint

,late_qtyint

,po_datevarchar(10)

)

insert into #mytable (Location,shipqty,late_qty,po_date)

select 'Home',2,3,'02/24/2012' union all

select 'Town',2,5,'01/20/2012' union all

select 'City',5,5,'01/20/2012' union all

select 'Home',4,5,'01/20/2012' union all

select 'City',6,5,'01/20/2012' union all

select 'Home',8,5,'01/20/2012' union all

select 'City',5,5,'01/20/2012' union all

select 'Town',7,5,'01/27/2012'

;

declare @FromDateas date = '2012-01-19'

declare @ToDateas date = '2012-02-23'

select

Location

,sum(shipqty)

,late_qty

,po_date

from #mytable mt

where

convert(varchar(10),po_date,103) >= @FromDate ----The convert here handles the date

andconvert(varchar(10),po_date,103) <= @ToDate ----The convert here handles the date

group by mt.Location, mt.late_qty, mt.po_date

drop table #mytable

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

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

Couple of errors in there, BWFC:

DECLARE @FromDate DATE = '2012-01-19'

DECLARE @ToDate DATE = '2012-02-23'

-- CONVERT po_date to DATE for the comparison,

-- using the correct STYLE argument for mm/yy/dddd

SELECT

Location,

SUM(shipqty),

late_qty,

po_date,

po_dateDATE = CONVERT(DATE,po_date,101)

FROM #mytable mt

WHERE CONVERT(DATE,po_date,101) >= @FromDate

AND CONVERT(DATE,po_date,101) <= @ToDate

GROUP BY Location, late_qty, po_date

^{“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 6

Hi thanks for ur reply.

I tried somewhat like this.

SELECT LOCATION,SUM(SHIPQTY) LATE_QTY ,PO_DATE FROM mytable WHERE DROP_DATE_STATUS = 'LATE'

AND SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),1,2) + '/' + SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),4,2)+'/'+

SUBSTRING(CAST(PO_DATE AS VARCHAR(11)) ,7,4) BETWEEN CONVERT(VARCHAR(11),'01/01/2013',103) AND CONVERT(VARCHAR(11),'01/31/2013',103)

GROUP BY LOCATION,PO_DATE

the query returns me value for jan 2012 instead of returning nothing

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

aar.mba (4/11/2014)
Hi thanks for ur reply.
I tried somewhat like this.
SELECT LOCATION,SUM(SHIPQTY) LATE_QTY ,PO_DATE FROM mytable WHERE DROP_DATE_STATUS = 'LATE'
AND SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),1,2) + '/' + SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),4,2)+'/'+
SUBSTRING(CAST(PO_DATE AS VARCHAR(11)) ,7,4) BETWEEN CONVERT(VARCHAR(11),'01/01/2013',103) AND CONVERT(VARCHAR(11),'01/31/2013',103)
GROUP BY LOCATION,PO_DATE
the query returns me value for jan 2012 instead of returning nothing

You've already established that this methodology is likely to fail - why pursue it when you've been offered an alternative which is shown to work?

^{“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

Neil Burton SSC-Insane Points: 23000 More actions · Answer 8

Thanks Chris. I got my date formats well and truly mixed up there! I'd put date in as the target but because I mis-read the styles it gave an error so I changed it varchar, my inexperience is showing. I'm still getting used to thinking in three date formats. When I first started yyyy-mm-dd seemed pointless and illogical but now it makes life much easier, no confusion about months and days with that.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

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

BWFC (4/11/2014)
Thanks Chris. I got my date formats well and truly mixed up there! I'd put date in as the target but because I mis-read the styles it gave an error so I changed it varchar, my inexperience is showing. I'm still getting used to thinking in three date formats. When I first started yyyy-mm-dd seemed pointless and illogical but now it makes life much easier, no confusion about months and days with that.

You're welcome. I can remember doing the same, but can't remember which millennium! Lowell and Lynn Pettis are both credited with some very comprehensive date/time documents - Google 'em and have a play. You'll pick it up in no time.

^{“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

Neil Burton SSC-Insane Points: 23000 More actions · Answer 10

Thanks once again. I've got one of Lynn's articles about dates saved, I've got one by Jeff Moden and I've got a really useful article about converts that I found elsewhere months ago and thought will do just the trick for this problem. Of course, the best written, most informative articles in the world mean nothing when you've got both eyes pointing in different directions and completely mis-read it. The main lesson here is to double check everything. As the phrase goes, 'measure twice, cut once'.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

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

Thank you very much.. this is what I expected.

But here comes another problem.That is the query works fine for "early" and "late" status.

For "ontime" its showing me conversion failed.

the problem is with data in PO_DATE column?

if so how can I find which row has problem and how to sort it out?

Could u pls advice

Neil Burton SSC-Insane Points: 23000 More actions · Answer 12

Could you post some sample data please? It will make it much easier to help if we can see the exact table format and the query you're running.

If you have a look at this article:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

it will give you some pointers on how to post it.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

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

pls find sample data...

CREATE TABLE #mytable

(

Location varchar(10),

Po_Date nvarchar(11),

Status varchar(10),

shipqty int,

)

insert into (location,Po_Date,Status,shipqty)

select 'a','01/01/2012','late',10 union all

select 'b','01/01/2012','ontime',10 union all

select 'c','01/02/2012','late',10 union all

select 'a','01/03/2012','ontime',20 union all

select 'b','01/04/2012','late',30 union all

select 'a','01/01/2012','late',10 union all

select 'b','02/01/2012','ontime',10 union all

select 'c','01/02/2012','late',10 union all

select 'a','01/03/2012','ontime',20 union all

select 'b','01/04/2012','late',30 union all

select 'a','01/01/2012','late',10 union all

select 'b','02/01/2012','ontime',10 union all

select 'c','01/02/2012','late',10 union all

select 'a','01/03/2012','ontime',20 union all

select 'b','01/04/2012','late',30 union all

;

declare @fromdate as date = '01/01/2012'

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

select location,sum(shipqty) from #mytable

where status = 'ontime' and Po_Date between convert(date,'01/01/2012',101) and convert(date,'01/04/2012',101)

group by location

the above select query I use for "ontime" and "late".i create two datasets based on these two criteria

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

So for Status "late" the query works fine.

For "ontime" the query throws conversion error.

The problem is with my UAT data?

if the query works fine with Status "late",why not with "ontime"?