All Forums Database
dmenzaghi 4 posts Joined 08/13
21 Aug 2013
Weekday and Time function

Hi all,
i'm new in this forum.
I have to simple problems that i cannot solve:
- I need to find a function that calculate in Teradata the weekday of a date variabile.
- i need to compare a Time variable whit some constant values:
 ie.  
  case
     when  var_time < '18:00' then 1
     when  var_time < '11:00' then 2
 
Thanks in advance for you help
D.
 
 

KS42982 137 posts Joined 12/12
21 Aug 2013

To find out the weekday of a date, you can use - SYS_CALENDAR.CALENDAR
To compare the time variable with the constant value, you can put the constant value in the quotes as you did in your example with the same format of your time variable.

dnoeth 4628 posts Joined 11/04
21 Aug 2013

There's no built-in function for weekday, but besides a calendar you might do a simple calculation, e.g. this will return monday as 1:
REPLACE FUNCTION day_of_week(cdate DATE)
RETURNS BYTEINT
SPECIFIC day_of_week_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN ((cdate - DATE '0001-01-01') MOD 7) + 1  
If you can't create SQL UDFs simply use the calculation as-is.
 
There's only one recommended qway to write a time literal: TIME '18:00:00'
Similar for dates: DATE '2013-07-21' and timestamps: TIMESTAMP '2013-07-21 18:00:00'
 
Dieter

Dieter

You must sign in to leave a comment.