Archives of the TeradataForum

Message Posted: Thu, 07 Jan 2016 @ 14:17:26 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Incorrect results - WEEK(CURRENT_DATE) and

From:		Dieter Noeth

Sravanv Bodla wrote:

In most of the reports WEEK(CURRENT_DATE) is used and it?s now giving incorrect results.

sel WEEK(CURRENT_DATE) .

This function is returning the current week as 2, which is not True.

There's no WEEK function in Teradata SQL, you run an ODBC-connected session and the ODBC-driver rewrites this to a stupid calculation, which happened to work last year :-)

Whereas, if I use

sel WEEKNUMBER_OF_YEAR (current_date)

the output is 1. --> FW 1. (which is true). My assumption is both the functions should return the output as 1. Can you please clarify?

WEEK_NUMBER returns a value based on the current session calendar, today (2016-01-07) all three return the same value 1, but on Jan. 1st:

     WEEKNUMBER_OF_YEAR(DATE '2016-01-01','ISO')        --> 53
     WEEKNUMBER_OF_YEAR(DATE '2016-01-01','TERADATA')   --> 0
     WEEKNUMBER_OF_YEAR(DATE '2016-01-01','COMPATIBLE') --> 1

Also, just curious to know how these results are fetched from the database?

Sel user

the session's logged on user, returned by the PE

Sel time

OS time of the node the session is connected to, returned by the PE

Select date

OS date of the node the session is connected to, returned by the PE

sel WEEKNUMBER_OF_YEAR (current_date)

returned by executing the function

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference