SSIS - type cast to MM/DD/YYYY from YYYYMMDD

Question

Post reply

SSIS - type cast to MM/DD/YYYY from YYYYMMDD

Viewing 14 posts - 16 through 29 (of 29 total)

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

themissnlink Ten Centuries Points: 1049 More actions · Answer 1

Something interesting I did find that works in an odd way is:

update temp_ameriflex_detail

set emp_doh = convert(char, getdate(), 112)

This works and sets all date to the sysdate, but when I run the code below it does nothing, but goes through successfully as if it did.

update temp_ameriflex_detail

set emp_doh = convert(char, emp_doh, 112)

Is this not how it supposed to look when updating data in the current column?

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

I think your

UPDATE temp_ameriflex_detail

SET emp_doh = convert(char,emp_doh,112)

completes successfully because it's not changing to a date, just formatting varchar as char, which isn't really changing anything.

Try this and see if you get results, if so you have data that isn't a date (part of the problem of not storing as datetime):

SELECT emp_doh

FROM temp_ameriflex_detail

WHERE isdate(emp_doh) = 0

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

themissnlink Ten Centuries Points: 1049 More actions · Answer 3

When I run that code I get back 7 NULL fields. Is this where my problem is coming from?

The file I am importing has over 10k records so I couldn't possibly catch that looking at the file.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Take a look at this little bit of test code and see if you can use what it demonstrates (at least it works on my system here at work).

declare @ADate varchar(10);

set @ADate = '3/9/2008';

select @ADate;

select @ADate = convert(varchar(10), cast(@ADate as datetime), 112);

select @ADate;

😎

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

worth a shot, if we try to parse a string that doesn't exist, it's likely to give us trouble. Any chance you know how to populate those fields?

If not, you probably want to use ISNULL() wrapped around our substring concatenation, with a default date of some sort.

Example:

UPDATE temp_ameriflex_detail

SET emp_doh = ISNULL(convert(datetime,

substring(emp_doh,CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1)+1,4) +

substring(emp_doh,1,charindex('/',emp_doh,1)-1) +

substring(emp_doh,charindex('/',emp_doh,1)+1,(CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1) - charindex('/',emp_doh,1))-1 )

,112),'12/31/2078')

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

themissnlink Ten Centuries Points: 1049 More actions · Answer 6

Yes that worked!

update temp_ameriflex_detail

set convert(varchar(10), cast(emp_doh as datetime), 112)

I took your code and edited it to fit my description. I am new at SQL so I didn't realize I had to combine the CONVERT and CAST together to get results.

Thank you all so much for your help.

Best Wishes.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Actually, I did a little more testing, and here is my code. The null value stayed null, no problems with the update.

create table #TestTab (

ADate varchar(10) null

);

insert into #TestTab

select '3/9/2008' union all

select null union all

select '10/1/2008' union all

select '10/10/2008';

select * from #TestTab;

update #TestTab set

ADate = convert(varchar(10), cast(ADate as datetime), 112);

select * from #TestTab;

drop table #TestTab;

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

The code could also be written this way:

create table #TestTab (

ADate varchar(10) null

);

insert into #TestTab

select '3/9/2008' union all

select null union all

select '10/1/2008' union all

select '10/10/2008';

select * from #TestTab;

update #TestTab set

ADate = convert(varchar(10), convert(datetime, ADate), 112);

select * from #TestTab;

drop table #TestTab;

😎

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

thanks Lynn, I knew there had to be a better way. 🙂

Why does that make a difference though? converting from datetime to varchar is implicit; converting from varchar to datetime is also implicit?

At least according to the chart in BOL.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

jcrawf02 (9/24/2008)
thanks Lynn, I knew there had to be a better way. 🙂
Why does that make a difference though? converting from datetime to varchar is implicit; converting from varchar to datetime is also implicit?
At least according to the chart in BOL.

Needed to do an explicit conversion from character to datetime to get convert to recognize that we were converting a datetime value to a character (string) value using the character format of 112 for the conversion.

To be honest, I'd rather see date values stored using the datetime data type (or date data type in 2008).

😎

jjfrick Valued Member Points: 55 More actions · Answer 11

Hello All,

I'm a newbie to ASP.NET and have been trying for a couple days now to output a derived row DT_DBTIMESTAMP as YYYY/MM/DD HH:MM:SS. In SSIS I'm using a flat file that has several dates stored as strings in the following format:

M/D/YYYY (8)

M/DD/YYYY (9)

MM/D/YYYY (9)

MM/DD/YYYY (10)

I came up with this formula that works great for me so I'm sharing it here if anyone is interested.

This is what I'm trying to do....

IF M/D/YYYY THEN (IF M/D/YYYY THEN format M/D/YYYY ELSE format M/DD/YYYY ) ELSE

IF MM/D/YYYY THEN (IF MM/D/YYYY THEN format MM/D/YYYY ELSE format MM/DD/YYYY) ELSE NULL(DT_DBTIMESTAMP)

and here's the code....

FINDSTRING(BaptismalDate,"/",1) == 2 ? (FINDSTRING(BaptismalDate,"/",2) == 4 ? (DT_DBTIMESTAMP)(SUBSTRING(BaptismalDate,5,4) + "-0" + SUBSTRING(BaptismalDate,1,1) + "-0" + SUBSTRING(BaptismalDate,3,1)) : (DT_DBTIMESTAMP)(SUBSTRING(BaptismalDate,6,4) + "-0" + SUBSTRING(BaptismalDate,1,1) + "-" + SUBSTRING(BaptismalDate,3,2))) :

FINDSTRING(BaptismalDate,"/",1) == 3 ? (FINDSTRING(BaptismalDate,"/",2) == 5 ? (DT_DBTIMESTAMP)(SUBSTRING(BaptismalDate,6,4) + "-" + SUBSTRING(BaptismalDate,1,2) + "-0" + SUBSTRING(BaptismalDate,4,1)) : (DT_DBTIMESTAMP)(SUBSTRING(BaptismalDate,7,4) + "-" + SUBSTRING(BaptismalDate,1,2) + "-" + SUBSTRING(BaptismalDate,4,2))) : NULL(DT_DBTIMESTAMP)

Hope it will save someone some time.

Jeff

kjjaison SSC Journeyman Points: 91 More actions · Answer 12

kjjaison

SSC Journeyman

Points: 91

September 22, 2011 at 1:24 am

#1384965

Thanks for your ans

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 13

Hi,

Try the following syntax:-

select convert(varchar(12),getdate(),101)

Result:-

9/19/2007

regards

Palash Gorai

Phil Parkin SSC Guru Points: 247180 More actions · Answer 14

palash.gorai (9/22/2011)
Hi,
Try the following syntax:-
select convert(varchar(12),getdate(),101)
Result:-
9/19/2007
regards
Palash Gorai

How is this relevant?