Convert integer to date
-
Hello,
In a flat file source (that comes from a Microsoft publishing "Training Kit - Implementing a Data Warehouse with Microsoft SQL Server 2012") i have integer based column representing birth date (BirthDate) , something like this 20170625. I can't convert it to a date datatype (YYYY-MM-DD) in SSIS package though i have tried to use following solution ConvertIntegerToDate . For testing purposes I've developed a simple package consisting a data flow task with Flat File Source, Dervied Column and a Multicast transformation and i am still getting the same error about failed conversion (ssis data conversion returned status value 2). Could you please help me to solve this problem? Flat file attached.
Lukas -
OK, here is a way of doing this. There must be a shorter way, but can't think of one right now.
(DT_DATE) (left((DT_STR, 8, 1252) @[User::DateKey],4) + "-" + SUBSTRING( (DT_STR, 8, 1252) @[User::DateKey] , 5,2 ) + "-" + RIGHT( (DT_STR, 8, 1252) @[User::DateKey], 2 ))
Replace @[User::DateKey] with your own variable.
Damned smileys! They should be colon D.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Something like this...
DECLARE @DateAsInt INT = 20170625;SELECT
DateAsDate = DATEFROMPARTS(SUBSTRING(dc.DateAsChar, 1, 4), SUBSTRING(dc.DateAsChar, 5, 2), SUBSTRING(dc.DateAsChar, 7, 2))
FROM
( VALUES (CAST(@DateAsInt AS CHAR(8))) ) dc (DateAsChar); - Phil Parkin - Monday, June 26, 2017 5:10 AM
Yeah, that's why I have the display emoticon setting off. I avoid seeing silliness like that, and if somebody uses an emoticon in a normal way, it's pretty obvious.
Of course, that only helps me, not everyone reading my posts, but hey, it's something.
Cheers!
-
First of all - DATE data type does not have formatting.
So, you need to define what do you actually need as an outcome - a DATE value or a CHAR(10) value in the specified format.If you actually need a CHAR string (say, for placing into another text file) then you simply CONVERT(CHAR(8), @DateAsInt) and then STUFF it with dashed in appropriate positions.
If you need a DATE value (for, say, storing in a base table) then you again start with converting to CHAR(8), but then convert to DATE using ISO datestyle in CONVERT function (112).
That would be running godzillion times faster than any solution using DATEFROMPARTS.
_____________
Code for TallyGenerator -
Thank you for answers!
I've tried to apply Phil's solution but i still have problems with appropiate conversion but to be honest i coludn't spent as much time on it as i should. I will give it one more try and then let you know about results. - lukaszpiech - Tuesday, June 27, 2017 5:51 AM
Do you actoually needa DATE or CHAR output?
_____________
Code for TallyGenerator -
I need DATE output.
-
Given the context (the Kimball method of DW), the intention is that any time you actually need a date, you join to the dim_Date table and get the actual date from there.
That said, I think the whole concept of a "Date Key" makes no sense considering that you can join to a dim_Date on an actual DATE data type just as easily as you can an INT data type... AND... You can actually treat your date keys as dates...
Check out the comments on this thread... https://www.sqlservercentral.com/Forums/1878742/Time-dimension-key-as-HHMM-text-or-HMM-int?PageIndex=2#bm1878904 -
For DATE output use my 2nd suggestion.
CONVERT (DATE, CONVERT(CHAR(8), DateAsInt), 112)
_____________
Code for TallyGenerator -
I haven't mentioned earlier but i would like to do all needed conversion in SSIS, like in derived column component.
It seemed to me like a simple task, import data from a flat file source (file attached in first post) to a table created on the basis of a code from a book:CREATE TABLE stg.CustomerInformation
(
PersonID INT NULL,
EnglishEducation NVARCHAR(30) NULL,
EnglishOccupation NVARCHAR(50) NULL,
BirthDate DATE NULL,
Gender CHAR(1) NULL,
MaritalStatus CHAR(1) NULL,
EmailAddress NVARCHAR(50) NULL
);
But nothing seems to work. As i mentioned, i've failed to implement Phil's solution so...back to the drawing board:( - lukaszpiech - Wednesday, June 28, 2017 12:56 PM
The thing is, I tested my solution and it worked just fine for me. So you need to describe in rather more detail what happened in your case. Include error messages, screen shots and whatever else you think will illuminate the problem you are having.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
A varchar in the format of ccyymmdd is a very safe and easy thing to convert straight to a date so I use it often.
DECLARE @i int = 20170628;
SELECT @i, CAST(CAST(@i AS varchar(8)) as date); -
Thanks for a quick replay!
Here are my thoughts:
1. I've tried to add new column and replace BirthDate column. Both methods failed.
2. I am working with SQL Server 2014 not 2012 like in the book
3. Package execution screen below:
4. Output message with errors
5. I've changed data type of BirthDate column in Flat File source to: Unicode string [DT_WSTR]That's all what comes to my mind at the moment. Maybe you will notice something in attached screens.
P.S.
@bill Talada, thank you for replay also! -
I notice your input file has headings in the first line. Are you skipping over the first line in your processing? If not, edit the input file to remove the first line and try again. I've never seen a semicolon separated file; usually people use tabs or commas.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply