Converting date to Varchar? and Varchar to Date?

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?

I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

This should give you an idea of how to handle these conversions:

DECLARE @.MyDateTimeValue datetime
SET @.MyDateTimeValue = '2006-03-26 00:00:00.000'

SELECT convert( varchar(10), @.MyDateTimeValue, 101 )

-
03/26/2006

SELECT cast( '03/26/2006' AS datetime )

2006-03-26 00:00:00.000

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie