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
How to Get Date from a String
Moderators: chulett, rschirm, roy
How to Get Date from a String
SCOTTDun
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:
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]
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers