DSXchange

Hi All

I am getting below problem plz look in to this.

My Database is: SQL Server2005
Target_column_Datatype: Datetime

Source
Posting_time
--------
200607
200708
210708

In Derivation:

If Len(DSLink6.POSTING_TIME) <>6 Then "1900-01-01 00:00:00" Else Left(DSLink6.POSTING_TIME,2) : ":" : Left(Right(DSLink6.POSTING_TIME,4),2) : ":" : Right(DSLink6.POSTING_TIME,2)

I am handling this convertion through above logic but here my problem is if length of the Posting_Time not equal to 6, then i need to hardcode by "1900-01-01 00:00:00 else i need to convert the Posting_time as 20:06:07. If I put target column datatype as NVarchar or Varchar it's working fine. But the target column is Timestamp.It's not converting the above logic. Any help for this Query.


Regards,
Kondeti

what is the target?? its file/dataset/database, and what database?

it's SQL Server2005 Database

Regards
Kondeti

The fact that you're using substring and concatenation suggests that you are generating a string data type.

Use a function like TimeToString() to convert the incoming string to a time, and then assemble CurrentDate() and that result into a timestamp.

Where I need to use TimeToString()? Here the problem is If i am not getting any thing from source(null or length <>6) then I need to hardcode with "1900-01-01 00:00:00". else time like 20:01:01. I try with so many way but i didn't succeed on this. Please help me.

I'm sorry, I haven't had any experience with SQL Server, but I thought that timestamp types have the following format:

YYYY-MM-DD HH:MM:SS

In your statement "1900-01-01 00:00:00" seems to be in the right format, but you are missing date portion in "20:01:01".

What is the data type of DSLink6.POSTING_TIME ?