TimeStamp problem

[kondeti]

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

[keshav0307]

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

[kondeti]

it's SQL Server2005 Database

Regards
Kondeti

[ray.wurlod]

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.

[kondeti]

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.

[bchau]

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".

[ray.wurlod]

What is the data type of DSLink6.POSTING_TIME ?