Need to check the date under which quarter it falls

Database
Enthusiast

Need to check the date under which quarter it falls

Need to check the date  under which quarter it falls,

1)      if date is between 30/Oct and 28/Jan then display message ‘With-in-Quarter’

2)      if date is less than  30/Oct and 28/Jan then display message ‘Prior-Quarter’

3)      if date is greater than  30/Oct and 28/Jan then display message ‘Future-Quarter’

 please can any one help for above requirement 

7 REPLIES
Senior Apprentice

Re: Need to check the date under which quarter it falls

You could use some more or less complex CASE calculation based on your definition of a quarter and the date range you want to cover.

But this looks like quarters of a financial year, so you probably got a calendar table doing that calculation and then it's easy...

Dieter

Enthusiast

Re: Need to check the date under which quarter it falls

Hi dnoeth,

Thank you for the responce......Can you please give me any example for above requirement.

Senior Apprentice

Re: Need to check the date under which quarter it falls

Something like

case

  when col mod 10000 <= 0128 or col mod 10000 >= 1030 then "With-in-Quarter"

  when col mod 10000 < 1030 and col mod 10000 >  0730 then "Prior-Quarter"

  else "Future-Quarter"

end

...

where col mod 10000 not between 0430 and 0730 --exclude Q4 whatever it is

Did you check for an existing calendar table?

Dieter

Enthusiast

Re: Need to check the date under which quarter it falls

Hi Dieter,

how to create a function for above requirement......can you please help me for that

Senior Apprentice

Re: Need to check the date under which quarter it falls

When you're on TD13.10 you can create a UDF for this.

When you tested your calculation you simply copy it to a SQL UDF:

REPLACE FUNCTION myfunc (d DATE)

RETURNS VARCHAR(15)

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

RETURNS NULL ON NULL INPUT

SQL SECURITY DEFINER

COLLATION INVOKER

INLINE TYPE 1

RETURN

   your CASE statement here

Dieter

Enthusiast

Re: Need to check the date under which quarter it falls

Hi Dieter,

Thank you for the quick responce.

I am on TD13.0....i am trying to create udf but i am getting below error like

CREATE FUNCTION Failed. 3707: Syntax error, expected something like a name or Unicode delimited identifier between teh 'LANGUAGE' keyword and the'SQL' keyword.

can you please guide me how to create an UDF on TD13.0

Senior Apprentice

Re: Need to check the date under which quarter it falls

If you're on TD13.0 you can't create SQL UDFs, they simply not supported.

Use your CASE as is or check your calendar table.

Dieter