One kind of transformation

original data type is string ,format like MM/dd/YYYY,also there exists null or "" value

I need to convert them into datetime,format like YYYY--MM-dd

any idea about this?

thanks in advance

Using a derived column and some simple string manipulation you could cut the string by the /'s and build it the way you want.|||

however, converting to datestamp failed

|||SSIS is rather rigid in it's conversion from string to DT.
See http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/14/16073.aspx for examples on strings that will work.|||

yes, it's not difficult to convert from string to DT

but i still could not figure out how to determine if the current value is null or not

just like that,

[value]!=""?(DT_Date)(): ....

how to define the otherwise portion? or other better method?

thanks

|||Use the isnull() function.|||

yes, i used

(trim([value])!=""&&isnul[value]==false)?(DT_Date)[value]: ....

but, how to deal with null or "" to convert to DT?

thanks

|||

Are you asking us what datetime value you should pass out if the incoming value is null? How do we know - that depends on your business rules.

I guess I'm misunderstanding.

-jamie

|||

Sorry for my explains not clear

I want to know how to let it work if value is null

many thanks

|||

(TRIM([value]) != "" && ISNULL([value]) == FALSE ) ? (DT_DBDATE)(SUBSTRING([value],FINDSTRING([value],"/",2) + 1,4) + "-" + SUBSTRING([value],1,2) + "-" + SUBSTRING([value],FINDSTRING([value],"/",1) + 1,2)) : NULL(DT_DBDATE)

this can work now, thanks

sql

OLEDB vs ODBC

Maybe this group is more responsive for this particular question. TIA.
-- Original Message --
From: "mason"
Newsgroups: microsoft.public.sqlserver.clients
Sent: Tuesday, February 21, 2006 5:01 PM
Subject: OLEDB vs ODBC

>I am new to SQL Server 2005. The native client supports both OLEDB and ODBC
> connections. Which one should I prefer and why? Thanks.ODBC is the older technology and is harder to use. Always use OLEDB where
possible as it is easier to implement and provides faster connections.
Ian Logan
"mason" wrote:

> Maybe this group is more responsive for this particular question. TIA.
>
> -- Original Message --
> From: "mason"
> Newsgroups: microsoft.public.sqlserver.clients
> Sent: Tuesday, February 21, 2006 5:01 PM
> Subject: OLEDB vs ODBC
>
>
>|||"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
>
While that is true, there are still quite a few legacy applications out
there that only support ODBC. As Ian said, in general use OLEDB when
possible, but you will probably still want to have the ODBC drivers loaded.
As an example, I believe (I could be wrong here) that Microsoft Visio (prior
to 2002) only used ODBC.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you very much. That's what I like hear. I am not sure about "easier"
part, but "faster connection" tells them apart.
"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
> "mason" wrote:
>|||Thanks. Since the native client is a single DLL supporting both OLEDB and
ODBC connections, this shouldn't be a problem. We are still going to support
ODBC connection (DSNless), but to make OLEDB the default configuration for
MSSQL2005.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:e3xajlIOGHA.668@.TK2MSFTNGP11.phx.gbl...
> "Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
> news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> While that is true, there are still quite a few legacy applications out
> there that only support ODBC. As Ian said, in general use OLEDB when
> possible, but you will probably still want to have the ODBC drivers
> loaded. As an example, I believe (I could be wrong here) that Microsoft
> Visio (prior to 2002) only used ODBC.
>
> Rick Sawtell
> MCT, MCSD, MCDBAsql