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
TimeStamp problem
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Use a function like TimeToString() to convert the incoming string to a time, and then assemble CurrentDate() and that result into a timestamp.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: