
I am sure we have all worked with ERP Applications that still store their dates and times in numeric or character fields. I was asked what would need to be done to convert these into a timestamp in a SQL Select statement. Personally, I do like using timestamps rather than individual date and time columns, or fields. Therefore, I consider this a good question.
I have a SQL DDL Table with a mixture of "date" and "time" columns within it:
- DATE_NBR8: A numeric representation of a date, eight long, and in ISO format (YYYYMMDD).
- DATE_NBR7: A numeric representation of a date, seven long, in CYMD format (CYYMMDD).
- DATE_CHAR: A character representation of a date, eight long, in ISO format with no separator characters.
- DATE_DATE: What I would call a "true date", date data type.
- TIME_NBR: A numeric representation of a time, six long.
- TIME_CHAR: A character representation of a time, six long, with no separator characters.
- TIME_TIME: A "true time", time data type.