SQL Server Data type convert using Derived Column [closed]

eiee3dmh 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(102)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.

Closed 8 days ago.
Improve this question

I used derive column to convert date from csv to DB type but I got this error message

This is the date type on csv.

This is the format

This is the table on db

I have tried to use Derived Column with two different expressions. This first:

and this is the second

(DT_DBTIMESTAMPOFFSET,7)(SUBSTRING(Horodatage,1,4) + "-" + SUBSTRING(Horodatage,6,2) + "-" + SUBSTRING(Horodatage,9,2) + " " + SUBSTRING(Horodatage,12,2) + ":" + SUBSTRING(Horodatage,15,2) + ":" + SUBSTRING(Horodatage,18,2))

None of them have worked for me, I always get this error message.

I wanted to fill the temp table with data from the csv but didn't find the right format to make it work.

sql-server

来源：https://stackoverflow.com/questions/77215105/data-type-convert-using-derived-column

1条答案

按热度按时间

jyztefdp1#

Assuming that the User::Horodatage variable values are along the following format:

2023/06/09 3:24:52 PM GMT+1

Step #1: Use the following SSIS expression

REPLACE( REPLACE( @[User::Horodatage], "/", "-"),"GMT+1","") + "+01:00"

It will output the following value:

2023-06-09 3:24:52 PM +01:00

Step #2: Keep the Derived Column Task output data type as DT_WSTR .

If your target column data type is DATETIMEOFFSET(3), upon insert it will be the following value in the database:

2023-06-09 15:24:52.000 +01:00

UPDATE

Here is an additional reproducible test.

DECLARE @tbl TABLE (dt DATETIMEOFFSET(3));
DECLARE @dt VARCHAR(30) = '2023-06-09 3:24:52 PM +01:00';

INSERT @tbl (dt)
SELECT @dt;

-- test
SELECT * FROM @tbl;

Output

dt
2023-06-09 15:24:52.000 +01:00

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Data type convert using Derived Column [closed]

1条答案

相关问题

热门标签

最新问答