如何将nvarchar转换为其他数据类型 [英] how to convert nvarchar to other datatype

问题描述

嗨..我想知道如何将一个表的nvarchar(max)列转换为第二个表的另一种数据类型.我创建了两个表:
一个是临时表，它包含一列名为COL1 nvarchar(max)的列，其中包含我使用sqlbulkcopy传输的整个文本文件. 第二个表是包含7列的原始表:

Hi..i want to know how can i convert column of nvarchar(max) of one table to another data type of column in second table.I have created two tables:
One is temporary table.It contains one column named as COL1 nvarchar(max) which contains the entire text file which i have transfered using sqlbulkcopy.
Second table is the original table which contains 7 columns:

Authdate datetime
BatchNo  nvarchar(30)
SeqNo nvarchar(30)
CardNo nvarchar(30)
TranType nchar(15)
AuthCode nvarchar(30)
Amount  float

我在临时表的Col1上使用Substring函数将数据插入原始表的相应列中.我编写了一个脚本，用于以这种方式将数据从一个表插入另一个表:

I am using Substring function on Col1 of temporary table to insert data into their respective columns in Original table.i have written a script for inserting data from one table to another in this way:

Insert into TblBank(AuthDate,BatbhNo,SeqNo,CardNo,TranType,AuthCode,Amount)
            Select Substring(col1,1,5),
                   Substring(col1,10,5),
                   Substring(col1,,5),
                   Substring(col1,1,5),
                   Substring(col1,1,5),
                   Substring(col1,1,5),
                   Substring(col1,110,6)
                    from TblTemp

如果AuthDate数据类型在原始表中为nvarchar，则此脚本有效.现在我想要的是如何将以下子字符串转换为各自的数据类型并将其插入到Main表中.我尝试使用CAST函数，但它向我展示如果AuthDate为日期时间，则显示错误参数1的数据无效".

我试图以这种方式编写脚本:

This script is working if AuthDate data type is nvarchar in original table.Now what i want is how can i convert the following Substring into thier respective datatype and insert it into the Main table.I have tried to use CAST function but it is showing me an error "invalid data for argument1" if AuthDate is datetime.

I have tried to write the script in this way:

Insert into TblBank(AuthDate,BatbhNo,SeqNo,CardNo,TranType,AuthCode,Amount)
            Select CAST(Substring(col1,1,5) AS datetime),
                   Substring(col1,10,5),
                   Substring(col1,,5), //dont remember the exact substring values
                   Substring(col1,1,5), //dont remember the exact substring values 
                   CAST(Substring(col1,1,5) AS nchar(15)),
                   Substring(col1,1,5),  //dont remember the exact substring values
                   CAST(Substring(col1,110,6) as float)
                    from TblTemp

但是上面的脚本不起作用..有人可以说我如何将一种数据类型转换为另一种数据.希望您理解我的疑问..

but the above script is not working..Can someone say how can i convert one datatype to another.Hope u understood my doubt..

推荐答案

当您说是无法正常工作"，我想您会遇到某种形式的转换错误?
始终希望SQL日期采用ISO 8601格式:"yyyy-mm-dd"，因此，如果您的NVARCHAR信息不是该格式，则确实会出现错误.由于您的数据只有五个字节，我想它肯定不是ISO格式的，可​​能是"DDMMY"格式或类似格式.您需要先将日期处理为正确的格式字符串，然后再将其传递给CAST.

如何执行将取决于您的数据格式-交给您！

When you say "is not working" I assume you get a conversion error of some form?
SQL Dates are always expected to be in ISO 8601 format: "yyyy-mm-dd", so if your NVARCHAR information is not in that format, then you will indeed get an error. Since your data is only five bytes, I am guessing it is definately not in ISO, possibly in "DDMMY" format or similar. You need to process the date into a proper format string before passing it to CAST.

How you do that will depend on your data format - over to you!

这篇关于如何将nvarchar转换为其他数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！