Cast-DATE

Database
Fan

Cast-DATE

Hi All,

I've novice to Terdadata & other databases.

I would like to know about below syntax, what does this statement mean? 

CAST(max(a11.XYZ_SK)-19000000 AS DATE)  ROAD

Thanks

8 REPLIES
Senior Apprentice

Re: Cast-DATE

 a11.XYZ_SK is an INTEGER column with YYYYMMDD, e.g. today is 20140120.

Substracting 19000000 results in an INTEGER with Teradata's internal date representation: 1140120

And the CAST changes the datatype to a DATE.

Storing a DATE as an INT is done in some calendar implementations. I never liked it, because you can't do any date arithmetic without doing a typecast like this.

Enthusiast

Re: Cast-DATE

Hi Dieter,

In our project,  date is stored in Decimal(16,0) and some dates are with 5 digits  91898  (mmddyy) , some dates are with 6 didgits 121898 (mmddyy)

we need to convert all these dates into a format of yyyy/mm/dd in the report.

we are using a case statment to segregate the dates as per number of digits and then converting to char , trim the last dot in the decimal , prefix a 0 for 5 digit dates and finally casting to date of requiered format.

I hope there will be an easy solution to it instead of these many conversions.  Any sugeestion on this please. are we doing right ?

Regards,

Sri

Senior Apprentice

Re: Cast-DATE

Hi Sri,

use your existing calendar or the built-in sys_calendar to calculate all possible values for your expected date range and then [left] join to it:

SELECT
(EXTRACT(MONTH FROM calendar_date) * 10000)
+ (EXTRACT(DAY FROM calendar_date)*100)
+ (EXTRACT(YEAR FROM calendar_date) MOD 100) AS strange_decimal_date
, calendar_date
FROM sys_calendar.calendar
WHERE calendar_date BETWEEN DATE '1980-01-01' AND DATE '2020-12-31'

If you need it repeatedly add it as a new column to your calendar.

Fan

Re: Cast-DATE

Thanks dnoeth for the information, it's helpful !

Enthusiast

Re: Cast-DATE

Thanks Dieter. It's good information. I'll try.

However i see calendar_date columns is of Date datatype so extract function work fine.

instead of calender_date if the column is of decimal data type how we can make it seperate to diplay as a date of requierd format.

Regars,

Sri

Senior Apprentice

Re: Cast-DATE

Hi Sri,

you can extract year/month/day using MOD/DIV, e.g.

CAST(((x MOD 100) + CASE WHEN (x MOD 100) < 20 THEN 100 ELSE 0 END) * 10000 -- year 19xx or 20xx
+ (x / 10000) * 100 -- month
+ (x / 100 MOD 100) -- day
AS DATE)
Fan

Re: Cast-DATE

Hi All,

CAST(max(a11.XYZ_SK)-19000000 AS DATE)  ROAD

The above syntax, gives results for todays date / current date.

How to get results for last week date / 2 weeks before.

Thanks

Enthusiast

Re: Cast-DATE

Thank you very much Dieter. It really helps !!!

Thanks Sai for your input.