All Forums Database
sai116 3 posts Joined 01/14
19 Jan 2014
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
 
 
 

dnoeth 4628 posts Joined 11/04
20 Jan 2014

 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.
 
 

Dieter

sgarlapa 88 posts Joined 03/13
20 Jan 2014

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

dnoeth 4628 posts Joined 11/04
20 Jan 2014

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.

Dieter

sai116 3 posts Joined 01/14
20 Jan 2014

Thanks dnoeth for the information, it's helpful !

sgarlapa 88 posts Joined 03/13
20 Jan 2014

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

dnoeth 4628 posts Joined 11/04
21 Jan 2014

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)

 

Dieter

sai116 3 posts Joined 01/14
21 Jan 2014

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

sgarlapa 88 posts Joined 03/13
22 Jan 2014

Thank you very much Dieter. It really helps !!!
Thanks Sai for your input.
 

You must sign in to leave a comment.