How to Get Date from a String

[ScottDun]

Hi,

I have a text file and there is a string 'PROVIDER20151120'. I need to extract that 20151120 and convert it into 2015-11-20. I used StringToDate(NYMMIS_TB_EXTR.CLOB [9,8], '%yyyy%mm%dd') to no avail. Any help would be great. Thanks

[chulett]

Looks like we need to chat about dates.

For the record, "2015-11-20" is not a date, it is a string formatted in the external representation of a date. An actual DATE field has no format and is basically stored internally as a large number, the number of offset days from a certain zero date - which is what the function you used outputs.

So, first question - do you need an actual DATE or just literally a string in the format that you showed us? If you're not sure, let us know what you're going to do with it, what the target for this data is. For the former, what you posted should work fine. For the latter, you could wrap it with a DateToString() and tell it the external format you want it back out in: %yyyy-%mm-%dd. Or simply substring and concatenate since both are strings:

Code: Select all

YourField[9,4] : '-' : YourField[13,2] : '-' : YourField[15,2]

[harikhk]

In case you need the output in the format you mentioned, your code would suffice.
Note that your output column should be of date datatype

[chulett]

In case you need the output in the format you mentioned, your code would suffice.

No, not really. There's no "output format" in their posted code. You are correct about the date target part, however.

[harikhk]

Agreed Chulett