Sunday, March 11, 2012

Date formats in SSIS

Hi once again guys,

I seem to be struggling with everything in SSIS these days!

I have a datetime field and I want to convert it to the following format in my derived column component :

yyyy.mm.dd

I also have another datetime field but this time I am only interested in the time values and I want to get :

HH:MM

How do I go about doing this in the SSIS expression builder?

Please help.

Sometime is easier to perform this kind of transforms right on the source query; but that depends on your level of confidence when writing SQL Vs SSIS expressions. In general I find SQL syntax more readable than its equivalent of SSIS expression.|||

Hi Rafael,

You are correct of course.

I do find it a lot easier to do this kind of thing in T-SQL but I am trying to use as much of SSIS as possible.

T-SQL :

convert(char(5), getdate(), 114) gets me the time in format HH:MM

convert(varchar, getdate(), 102) gets me the date format I desire

BUT this is about SSIS expressions not T-SQL for me :)

Anyway, in the end I used a series of DATEPART functions to get the year, month and days and concatenated together to form my YYYY.MM.DD string.

Thanks for your input.

|||

dreameR.78 wrote:

the year, month and days and concatenated together to form my YYYY.MM.DD string.

I was just about to suggest that :)

its the best way when using expressions.

-Jamie

|||

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

|||Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)|||

dreameR.78 wrote:

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Error message?

|||

Phil Brammer wrote:

Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Perhaps parenthesis around the last if-then-else statement?

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + (LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))|||

Hi Phil,

Actually I made a formatting mistake,

The exprerssion is all the 4 lines!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)

gets validated correctly.

All the above is aying is get the minues datepart from the datetime field and if it is on digit value then prefix it with a zero else return it the way it is.

The second part of the xpression is pretty much idetical to the above only it rteurns the seconds datepart from my datetime field.

I am concatenating : so that my time appears as MM:SS

Should be simple really but my expression is highlighted in red for some reason. But as I said, the above code on it's on, i.e. just the MM works fine, it's only when I include the second part that the expression fails to evaluate.

|||Hi again Phil, that's exactly what I tried before posting this but even though the expression evaluated. The return result was not showing the seconds. Not even a doble zero!|||

Hi Jamie,

The error message isn't very specific to be honest, but the expression is highlihted in red so obviously it doesn't like something about it.

I'm getting depressed now.

|||You have to surround your conditional statements with parenthesis and then concatenate them with the "+" symbol:

(LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime))

+ ":" +

(LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))

Also, ensure that the output type of the column is string.|||

Hi Phil,

Ah sport on. Well done my friend! It's now working.

I only had brackets for the second part but when I also included them for the first part, the result came out as expected.

A good way to end the week.

Have a good weekend all.

No comments:

Post a Comment