what is the SQL to display a number (200909) as Sep 2009 in Teradata 12

Question

what is the SQL to display a number (200909) as Sep 2009 in Teradata 12

Answer 1

I'm not familiar with Teradata, but the general approach would be:

1. Convert your number into a string that could be converted into a date (i.e. append a "01" to the end to get "20090901")
2. Convert that string into a date type
3. Use date functions to render "Sep 2009" (maybe a month function and a year function)

Good luck, a Google search could probably tell you how to proceed with each of the steps above.

Answer 2

Teradata stores dates as integers in the following manner:

(year - 1900) * 10000 + (month * 100) + day

So you'd remove 1900 from the year and add a day to make the number a valid date, then cast to a format that only shows the month and year.

SELECT CAST(CAST((200909 - 190000) * 100 + 1 AS DATE FORMAT 'm3by4') AS CHAR(8));