0

I have to send data from csv into SQL DB. Problem starts when I try to convert data into Int. It wasnt my idea and I really cant do much with this datatype. When I'm trying to achieve this problem pop up:

Data Conversion 2: Data conversion failed while converting column "pr_czas" (387) to column "C pr_dCz_id" (14). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Tried already to ignore this problem but then another problems came up so there is no other way than solving this.

I have to convert this data from csv file which is str 50 into int 4

It must be int4. One of the requirements Dont know what t odo.

This is data I'm trying to put into int4. Look on pr_czas Here is how data looks

This is data's datatype enter image description here

Before I tried to do same thing with just DD.MM.YYYY but got same result...

Improve this question
7
  • 1
    First of all, do you try to convert date datatype to convert to int or some other datatype to int? If you try to convert date to int, you can't! Commented Apr 16, 2021 at 9:07
  • @Michal Do you mean for example convert 16/04/2021 to 16042021 ? Commented Apr 16, 2021 at 13:55
  • As others have indicated, provide a minimal reproduction of your data otherwise we could be solving the wrong problem. Commented Apr 16, 2021 at 19:55
  • @MarkoIvkovic I have updates topic, please reply if you can Commented Apr 22, 2021 at 19:52
  • @billinkc Data is 31.01.2020 00:00 Commented Apr 22, 2021 at 19:54

1 Answer 1

Reset to default
1

Given an input column named [pr_czas] that contain string values that look like 31.01.2020 00:00 which appears to be a formatted date time represented in the format "DD.mm.YYYY HH:MM", I would like to express that as a whole number DDMMYYHHMM

Add a derived column to your data flow and call this new_pr_czas

The logic I'm going to use is a series of REPLACE statements and cast the final result to an integer. Replace the period, replace the colon and the space - all with nothing

(DT_I8)REPLACE(REPLACE(REPLACE([pr_czas], ".", ""), ":", ""), " ", "")

This is an easy case but things to note.

An integer/int32/I4 has a maximum value of 2 billion. 310120200000 is too large to fit into that space so you would need to make that an bigint/int64/I8. If I remember your previous question, you were having troubles with a lookup task so this data type mismatch might hurt you there.

The other thing to be aware of is that leading zeros will be dropped when converted to a number because they are not significant. If you need to retain the leading zeros, then you're working with string data type. This is an advantage to working with the ISO standard but if your data expects DD, then far be it for me to say otherwise.

If you need to slice your date into another format, then you'll want to have a few derived columns. The first one will generate a string column for each piece of pr_czas - year, month, day, hour and minute. You'll use the substring method for this and findstring to find the period space and colon. The next data flow will be used to put those string pieces back into the new format and cast that to I8. Why? Because you can't debug doing it all in one shot but you can put a data viewer between two derived columns to figure out where a slice went awry.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.