Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Conversion error

Author  Topic 

aarthirsk
Starting Member

5 Posts

Posted - 2014-04-11 : 06:23:58
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.



the data looks like mm/dd/yyyy format but the data type is n varchar

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-11 : 08:53:49
Are you missing a where clause in your query?

Also, is PO_DATE NVARCHAR? IF so, I have no problem with code like this:


declare @strdate as nvarchar(20) = '04/11/2014'

select cast(@strdate as date)


Finally, SUBSTRING will fail in your query since it wants to work with strings (hence the name)
Go to Top of Page
   

- Advertisement -