DT_BYTE to DT_DBDATE

Question

DT_BYTE to DT_DBDATE

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

David Burrows - Monday, November 5, 2018 9:21 AM
Jeff Moden - Monday, November 5, 2018 9:00 AM
Thanks, David. With the understanding that I don't know SSIS syntax, would (DT_DATE)((DT_STR,10,1252)SUBSTRING(Date,2,10) do the same thing or do you really have to do the character by character conversion using individual SUBSTRING(pos,1) like you did?
SSIS has no HEX to string conversion functionality, only integer to HEX.
Typical MS :angry:

Ok... thanks for the info, David.

Since you know me, it'll probably come as no surprise to you that I look at this as yet another reason for me to avoid SSIS. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))

EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...

Heh... even with that, I'm still hating SSIS for this type of stuff. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65138 More actions · Answer 4

sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...

Except for 0x323031342D30342D3033
:Whistling:

Far away is close at hand in the images of elsewhere.
Anon.

sgmunson SSC Guru Points: 110639 More actions · Answer 5

David Burrows - Friday, November 9, 2018 9:20 AM
sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...
Except for 0x323031342D30342D3033
:Whistling:

2014-04-03 is the result string. Why is that a problem?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

David Burrows SSC Guru Points: 65138 More actions · Answer 6

sgmunson - Wednesday, November 14, 2018 6:24 AM
David Burrows - Friday, November 9, 2018 9:20 AM
sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...
Except for 0x323031342D30342D3033
:Whistling:
2014-04-03 is the result string. Why is that a problem?

Because it doesn't, it produces 2014-04-0
🙂

Far away is close at hand in the images of elsewhere.
Anon.

sgmunson SSC Guru Points: 110639 More actions · Answer 7

David Burrows - Thursday, November 15, 2018 2:00 AM
sgmunson - Wednesday, November 14, 2018 6:24 AM
David Burrows - Friday, November 9, 2018 9:20 AM
sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...
Except for 0x323031342D30342D3033
:Whistling:
2014-04-03 is the result string. Why is that a problem?
Because it doesn't, it produces 2014-04-0
🙂

Then repeat the (DT_STR,10,1252) between the replace functions until you find which one shortens it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 8

sgmunson - Monday, November 19, 2018 12:14 PM
David Burrows - Thursday, November 15, 2018 2:00 AM
sgmunson - Wednesday, November 14, 2018 6:24 AM
David Burrows - Friday, November 9, 2018 9:20 AM
sgmunson - Friday, November 9, 2018 6:48 AM
Doesn't have to actually be that long. Once you have the human readable string, here's the SSIS code to get it into a date:
(DT_DATE)((DT_STR,10,1252)REPLACE(REPLACE(REPLACE('0x323031342D30342D3037', '0x', ''), '2D', '-'), '3', ''))
EDIT: you might or might not need the REPLACE that turns 2D into a dash. You can test that...
Except for 0x323031342D30342D3033
:Whistling:
2014-04-03 is the result string. Why is that a problem?
Because it doesn't, it produces 2014-04-0
🙂
Then repeat the (DT_STR,10,1252) between the replace functions until you find which one shortens it.

Heh... hating SSIS a little more with each passing post. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16020 More actions · Answer 9

why not just convert the date to a string on the source select - converting to a format that SSIS knows well will then make the conversion to date straightforward.

And if the source is not a select (where you could change the format) then passing it through a c# script component would make it a lot easier to do the conversion than trying to mess around with a data conversion task - which if it needs to be changed going forward will be another pain to get right.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 10

Or... you could do it in T-SQL and not have to worry about SSIS or C#. 😉 Maybe even a persisted computed column on the table might work, eh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jklenk86 Old Hand Points: 388 More actions · Answer 11

frederico_fonseca - Monday, November 19, 2018 5:11 PM
why not just convert the date to a string on the source select - converting to a format that SSIS knows well will then make the conversion to date straightforward.
And if the source is not a select (where you could change the format) then passing it through a c# script component would make it a lot easier to do the conversion than trying to mess around with a data conversion task - which if it needs to be changed going forward will be another pain to get right.

This was the answer. Thanks to everyone who jumped in with ideas.

After a couple days battling SSIS to get this to work, I looked up data type conversions in MySQL and set the conversion in the source.

Ultimately, this probably should have been the first thing I tried. I appreciate everyone's ideas though, it was helpful and I learned a lot about conversions in SSIS.