Load date time field to date field

Question

Load date time field to date field

only4mithunc

SSCrazy Eights

Points: 8434
More actions
December 21, 2015 at 8:46 am

#307241

Hi All,
I have a flat file source with a field of type date time ( it has got the data in Year-Month-Day-Hour-Minute-Second-Millisecond format )
In the destination table I have the column as Date type ( YYYY-MM-DD). I tried using derived column and then get the YYYY +"-"+MM+"-"+DD , but that didn't work. Another option I tried was Data Convertion task and tried to covert to DT_DATE but that also didn't help.
Can some one help me to resolve this ?
Thanks & Regards,
MC

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

only4mithunc (12/21/2015)
Hi All,
I have a flat file source with a field of type date time ( it has got the data in Year-Month-Day-Hour-Minute-Second-Millisecond format )
In the destination table I have the column as Date type ( YYYY-MM-DD). I tried using derived column and then get the YYYY +"-"+MM+"-"+DD , but that didn't work. Another option I tried was Data Convertion task and tried to covert to DT_DATE but that also didn't help.
Can some one help me to resolve this ?

It's not clear what you're actually having a problem with. Are you having problems actually loading a table from the flat file source or ???

--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)

Y.B. SSChampion Points: 11649 More actions · Answer 2

I'm not 100% if it behaves differently in SSIS but going from a DATETIME to a DATE data type is an implicit conversion.

CREATE TABLE #before (myDatetime DATETIME)

INSERT INTO #before

VALUES(GETDATE())

CREATE TABLE #after (myDate DATE)

INSERT INTO #after

SELECT myDatetime FROM #before

SELECT * FROM #after

DROP TABLE #before

DROP TABLE #after

It looks like you are talking about a string concatenation in your post which make me suspicious. Is your destination column really a DATE type or a string?

I tried using derived column and then get the YYYY +"-"+MM+"-"+DD

SELECT quote FROM brain WHERE original = 1
0 rows returned

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

Perhaps what you need is a 'Derived Column Transformation' task between your Source and Destination that uses expression and leverage SUBSTRING function to build string in format YYYY-MM-DD and also recast as type DT_DBDATE (the SSIS equivalent of Date type).

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Hi All,

Thanks for your response.. I will make my requirement more clear..

I have a flat file source... and in that I have a column say C1 with date time value as for example;

2015-12-22-20:21:43.370000 . (this file is getting data from SAS ). and I'm trying to load this file to my SQL table... in SQL table I have created column of type DATETIME2. But I'm getting error as data is getting truncated...

I have observed that there an extra '-' between date and time... so I tried removing that one.. and also by adding one more '0' at the get to get 7 digits for millisecond... ( so that it will match to DATETIME2 ) but still no luck... is there any way to load the data in above format to SQL table ?

Thanks & Regards,
MC

ben.loudon Valued Member Points: 57 More actions · Answer 5

Have you tried creating a derived column after the source and using SUBSTRING to extract the first 10 characters to a new column and mapping this to your date column. If you leave this as Datatype String when you insert into a date it should implicitly convert it ( hopefully 🙂 )

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Hello Ben, thanks for your response... I trying that but not able to get the time along with it... the string when it get inserted to the table the time part it going as 00:00:00.0000000

Thanks & Regards,
MC

ben.loudon Valued Member Points: 57 More actions · Answer 7

Ah OK so you want the time component in the SQL table as well? I will have another go at this but you can probably do it using a derived column cast it to DT_DBTIMESTAMP by doing something like this

http://stackoverflow.com/questions/6256168/how-to-convert-a-timestamp-in-string-format-to-datetime-data-type-within-a-packa

Phil Parkin SSC Guru Points: 246965 More actions · Answer 8

Have a look here.

This gives you an idea of data type mappings between SQL Server and SSIS.

The implication of the link is that you should attempt to get your input data into an SSIS column of type (DT_DBTIMESTAMP2, «scale») (via a derived column transformation) and then map that column to your target column.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Y.B. SSChampion Points: 11649 More actions · Answer 9

Something like this worked for me. I used your example and striped out the problem hyphen and inserted into a DATETIME2 column.

DECLARE @myDateString VARCHAR(30) = '2015-12-22-20:21:43.370000'

DECLARE @test-2 TABLE (myDate DATETIME2)

INSERT INTO @test-2

SELECT

REVERSE(STUFF(REVERSE(@myDateString), CHARINDEX('-',REVERSE(@myDateString)), 1, ' '))

SELECT * FROM @test-2

Just an example on how to do the conversion...you'll need to rework your solution of course.

SELECT quote FROM brain WHERE original = 1
0 rows returned

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 10

Thanks every one... the above solutions worked... If I want directly load to main table then I can use type cast in Advanced tab... and if first load to staging table and then to main table.. then first I read the data as string into stating table and then wile inserting into the main table from staging table I used sub string option.. Thanks to all.

Thanks & Regards,
MC