DSXchange

Hi Guys,

Need to check the format of the incoming values is
YYYY-MM-DD HH:MM:SS.XXX

I need to find the last 3 digits i.,e XXX if it exists I need to take the left 19 digits.

My source is SQL server and column is Nvarchar and the output column is also nvarchar.
2011-07-01 00:00:00.000
2010-10-05 00:00:00.000

Iam trying to use the below syntax, is this correct.
Any idea would be of great help.

Thanks,

It's not correct. You need to check validity BEFORE attempting to convert.

Is this an interview question? You're the third to have asked it in the last couple of weeks.

No, its not interview question.

Thanks,

This works correctly for me :

If IsValid('Timestamp',StringToTimestamp(INPUTCOL,'%yyyy-%mm-%dd %hh:%nn:%ss.3')) then left(INPUTCOL,19) else "0"

As noted, you should be checking for validity BEFORE doing any conversion, the goal being to ensure that the conversion will work properly. The IsValid() function expects a string as its input so that isn't really working like you think it is.

We would have to convert to timestamp before checking for validity of timestamp. We cannot check for timestamp validity without the string being a timestamp. I tested with an invalid string and the conversion fails and consequently the validity fails and 0 is returned as is the intended purpose.

ssnegi wrote:We would have to convert to timestamp before checking for validity of timestamp.

Sorry but this is not correct.

Well it doesnt make any difference if we do the conversion before or after the validity. I tested with the conversion before validity and it works correctly.

Did you "test" it with a string that contained an invalid timestamp?

For example 2013-13-13 13:13:13

or even 2013-02-27T03:13:14 when your default timestamp format string does not include the "T"?

I tested string given by you...2013-02-27T03:13:14...The job output 0. It only gave a warning for validity.
APT_CombinedOperatorController,0: Data string '2013-02-27T03:13:14' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss.3': missing the fractional part of the field expected by tag %ss.3.

I also tested 2013-02-41 03:13:14.000 with DD=41 and it output 0 without warning.

If you test a string for validity FIRST and then convert only valid values, you would not receive warnings like that... which is the whole point of testing validity first.

srini.dw: did you get this working for you? Remove the StringToTimestamp from your code and for the "then" do whatever substring / conversion you need to populate the target field. For the "else" set it to whatever it should be if the validity check fails - zero, null, the original field value, whatever.

Thanks guys,

Our source was SQL server, we did a CAST statement in the source query, did not do any datetime format conversation.

Thanks,