How to convert nvarcahr to datetime ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

I have created a store procedure which gives me the following error.

"
Error converting data type nvarchar to datetime.

"

i am using the following querry.

SQL

ALTER procedure [dbo].[pay_res_report] 


@fd datetime,
@ld datetime    ,
@id nvarchar(20) ,
@s nvarchar(20) 

as
begin


declare @payable nvarchar(100)
declare @resable nvarchar(100)
declare @payable_adj float
declare @resable_adj float
declare @name nvarchar(100)
declare @op_balance float
declare @paid float
declare @received float
declare @Main_Opening_Balance float
declare @count int
 set @count = 0
declare @coun int 

if @s = 'c' 
begin

set @name = ( select CUS_NAME from  TBL_CUSTOMER where CUS_CODE = @id)

--declare @fd nvarchar(100)
--set @fd = '9/5/2012'
--declare @id nvarchar(100)

--set @id = '1'


-- ob + R/a + paid -p/a- received

set @op_balance = ( select  cast( CUS_CR_LIMIT as float) as ob from  TBL_CUSTOMER where CUS_CODE = @id)
set @paid       = isnull((select   sum(cast( PR_AMOUNT as float)) as paid  from PR where  PR_TYPE = 'Paid'  and   cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer'),0)
set @received       = isnull((select sum( cast( PR_AMOUNT as float)) as paid  from PR where  PR_TYPE = 'Received'  and   cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer'),0)

set @payable     = ( select  isnull( sum( cast(PI_TOTAL_AMOUNT as float) ) , 0) from  SPI_MAIN_INFORMATION  where CAST(PI_DATE AS DATETIME) < @fd and PI_CUSTOMER_ID = @id )
set @resable     =  (select  isnull(sum( cast(SI_TOTAL_AMOUNT as float) ),0) from  SSI_MAIN_INFORMATION  where CAST(SI_DATE AS DATETIME) < @fd and SI_CUSTOMER_ID = @id   )


set @payable_adj     = ( select  isnull( sum( cast(PI_TOTAL_AMOUNT as float) ) , 0) from  SPI_MAIN_INFORMATION  where CAST(PI_DATE AS DATETIME) >= @fd and CAST(PI_DATE AS DATETIME) <= @ld and PI_CUSTOMER_ID = @id )
set @resable_adj     =  (select  isnull(sum( cast(SI_TOTAL_AMOUNT as float) ),0) from  SSI_MAIN_INFORMATION  where CAST(SI_DATE AS DATETIME) >= @fd and CAST(SI_DATE AS DATETIME) <= @ld and SI_CUSTOMER_ID = @id   )

--set @Main_Opening_Balance =  ((@op_balance + @received) - @paid )
set @Main_Opening_Balance =  ((@op_balance + @resable + @paid) - (@payable -@received))

--select @payable as payable , @resable as resable , @op_balance as openbal  , @paid as paid , @received as received , @Main_Opening_Balance as main

 
 
 
set @coun =  (   select count(PR_CODE) as person   from (


select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,PR_AMOUNT as P ,'' as R  from PR

where  PR_TYPE = 'Paid'  and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0


union all

select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,'' as P ,PR_AMOUNT as R  from PR

where  PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd)  and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0


) b
)



--select @Main_Opening_Balance


if @coun =0
begin


select  'N/A' as PR_CODE ,'N/A' as PR_PERSON ,'N/A' as PR_PERSON_DESIGNATION  ,'N/A' as PR_TYPE  ,'N/A' as PR_DATE ,'N/A' as PR_PAY_MODE , cast (0 as float) as Paid  , cast( 0 as float) as Rece  , cast( 0 as float) as R , @name as Name,cast ( @Main_Opening_Balance as float) as ob , @payable_adj as adj_pay,@resable_adj as  adj_res


end
else
begin




select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE , cast (P as float) as Paid  , cast( R as float) as Rece  , cast( R as float) ,Name,cast ( OB as float) as ob , @payable_adj as adj_pay,@resable_adj as  adj_res from
(
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,PR_AMOUNT as P ,'' as R , @name as Name , @Main_Opening_Balance as OB from PR

where  PR_TYPE = 'Paid'  and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0


union all

select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,'' as P ,PR_AMOUNT as R , @name as Name , @Main_Opening_Balance as OB from PR

where  PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd)  and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0





)a


end

--set @count =0
--		set @count = ( select count(PR_CODE) from a  )
	

end


if @s = 's' 
begin

set @name = ( select SUP_NAME from  TBL_SUPPLIER where SUP_CODE = @id)
set @op_balance =  (select cast(SUP_EMAIL as float) as ob from  TBL_SUPPLIER where SUP_CODE = @id)
set @paid       = isnull((select  sum(cast( PR_AMOUNT as float)) as paid  from PR where  PR_TYPE = 'Paid'  and   cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'),0)
set @received       = isnull((select  sum(cast( PR_AMOUNT as float)) as paid  from PR where  PR_TYPE = 'Received'  and   cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'),0)

set @Main_Opening_Balance =  ((@op_balance + @received) - @paid )



 
 
set @coun =  (   select count(PR_CODE) as person   from (


select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,PR_AMOUNT as P ,'' as R  from PR

where  PR_TYPE = 'Paid'  and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0


union all

select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,'' as P ,PR_AMOUNT as R  from PR

where  PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd)  and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0


) b
)



--select @Main_Opening_Balance


if @coun =0
begin
select  'N/A' as PR_CODE ,'N/A' as PR_PERSON ,'N/A' as PR_PERSON_DESIGNATION  ,'N/A' as PR_TYPE  ,'N/A' as PR_DATE ,'N/A' as PR_PAY_MODE , cast (0 as float) as Paid  , cast( 0 as float) as Rece  , cast( 0 as float) as R , @name as Name,cast ( @Main_Opening_Balance as float) as ob  
end
else
begin




select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE , cast (P as float) as Paid  , cast( R as float) as Rece  , cast( R as float) ,Name,cast ( OB as float) as ob from
(
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,PR_AMOUNT as P ,'' as R , @name as Name , @Main_Opening_Balance as OB from PR

where  PR_TYPE = 'Paid'  and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'


union all

select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION  , PR_TYPE  ,PR_DATE ,PR_PAY_MODE ,  PR_AMOUNT ,'' as P ,PR_AMOUNT as R , @name as Name , @Main_Opening_Balance as OB from PR

where  PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld  and  cast( PR_DATE as datetime) >= @fd)  and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'





)a


end
--set @count =0
--		set @count = ( select count(PR_CODE) from a  )
--	
--
--end
--
--
--else  if @count = 0
--begin
--
--
-- select @name as [Name] ,cast(@op_balance as float ) as ob
--
--
--	
--			
end

how can i remove this error. plzen help me!!!!

Posted 24-Oct-12 22:37pm

Asim8748

Updated 24-Oct-12 22:44pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Answer 1 · 2012-10-25T04:07:00

You can CAST from NVARCHAR to DATETIME but I do not think that is your problem.

SQL

declare @t1 NVARCHAR(20) = '10/20/2012'
select CAST(@t1 AS DATETIME)

This works fine.

Break down your code into small chunks to find where the break is.

Also note that float is an "Approximate Numeric" versus an "Exact Numeric". See BOL "Data Types (Transaction-SQL)".

I agree with Richard that you should not store Dates as character strings if possible.

Richard MacCutchan · Answer 2 · 2012-10-24T22:49:00

Solution 1

You cannot cast a character string to a DateTime object, you need to convert it. Also, you should not store dates as character strings in your database; that is what the DateTime type was created for.

[edit]
djj55[^] points out that you can use a CAST in SQL like above; I stand corrected.
[/edit]

Posted 24-Oct-12 22:49pm

Richard MacCutchan

Updated 25-Oct-12 7:19am

v3

Comments

Corporal Agarn 25-Oct-12 13:25pm

Most of the time I use CONVERT when it comes to dates, but I have use CAST when I get lazy.

Richard MacCutchan 25-Oct-12 13:29pm

So does it do exactly the same thing?

Corporal Agarn 25-Oct-12 14:51pm

Depends on what you want. You can make CONVERT give the same thing as CAST, but as you know CONVERT has multiple options. For many of the queries we use here I use CAST(mydate AS DATE) for a compare.