Need to check the datetime format

[srini.dw]

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.

Code: Select all

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

Any idea would be of great help.

Thanks,

[ray.wurlod]

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.

[srini.dw]

No, its not interview question.

Thanks,

[ssnegi]

This works correctly for me :

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

[chulett]

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.

[ssnegi]

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.

[chulett]

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

Sorry but this is not correct.

[ssnegi]

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.

[ray.wurlod]

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

[ssnegi]

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.

[chulett]

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.

[srini.dw]

Thanks guys,

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

Thanks,