Changing a numeric field to a data field

[GGRush]

I have a field pulling in as "22,117" when the field is really a date field that should be displayed as "02/21/2017".  How change a make this change.  I know I can use "Substring" but when I move to a month with two digits my field will be inaccurate if I do not touch the "case" again.

[CognosPaul]

Which database are you using and what does the date 2017-FEB-02 look like?

[GGRush]

The database is DB2 .. we are using TransactSQL and the current field appears as "22,177" when I display the results.

[CognosPaul]

I need to know what Feb 2 looks like so I can suggest a course of action. If the value being returned in mdyy the course of action is different than if the value is mddyy. It becomes fun, because then you interrogate the value with various logic expressions to figure out what it actually is. Is 1117 2017-JAN-11?

If all you need is to add a 0, you can do lpad([field],6,'0') that will ensure 2017-FEB-21 looks like 022117, while 2017-DEC-21 will look like 12212017.

The real question is, why does your database have this field? mddyy is an entirely useless way of encoding a date. You can't sort it, and converting is a pain. Go to your DWH architect and smack him, then buy a bottle of the finest Finnish water for your friendly ETL guy to fix it into something more reasonable.

[Lynn]

Go to your DWH architect and smack him, then buy a bottle of the finest Finnish water for your friendly ETL guy to fix it into something more reasonable.

Sage advice!