怎么把nvarcahr转换成日期时间? [英] How to convert nvarcahr to datetime ?
本文介绍了怎么把nvarcahr转换成日期时间?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我创建了一个存储过程，该过程给了我以下错误.
"
将数据类型nvarchar转换为datetime时出错.
"
我正在使用以下Querry.
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.
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!!!!
推荐答案
您可以将NVARCHAR的CAST转换为DATETIME，但我认为这不是您的问题.
You can CAST from NVARCHAR to DATETIME but I do not think that is your problem.
declare @t1 NVARCHAR(20) = '10/20/2012'
select CAST(@t1 AS DATETIME)
效果很好.
将您的代码分解成小块，以找到中断所在的位置.
还要注意，浮点数是近似数值"与精确数值".请参见BOL数据类型(Transaction-SQL)".
我同意Richard的看法，如果可能的话，您不应该将Dates存储为字符串.
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.
您不能将字符串转换为DateTime对象，需要对其进行转换.另外，您不应该将日期作为字符串存储在数据库中.这就是创建DateTime类型的目的.
[edit]
djj55 [
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 aCASTin SQL like above; I stand corrected.
[/edit]
这篇关于怎么把nvarcahr转换成日期时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
