Get Date Function

[strom99]

Is there a way to Get Date?

I need to be able to determine the current date and also first day this month and first day next month.

CASE WHEN ORDERS.SHIP_DATE > first day next month THEN
ORDERS.LI_VALUE_AMT ELSE 0 END AS FUTURE ORDERS.

 

[tdatgod]

Hi,
My SQL is a little rusty but given a few CASTS, a couple functions and a lot of parentheses I was able to generate.....

sel (((current_date ( char(5) )) || '/01') (date)) (named firstofthismonth);

firstofthismonth
----------------
01/08/01


sel ((((current_date ( char(5) )) || '/01') (date)) + interval '1' MONTH) (named firstofnextmonth);

firstofnextmonth
----------------
01/09/01

sel (((current_date ( char(5) )) || '/01') (date)) (named firstofthismonth), (firstofthismonth + interval '1' MONTH) (named firstofnextmonth);

firstofthismonth firstofnextmonth
---------------- ----------------
01/08/01 01/09/01




Now how you put this mess into your CASE statement I don't know. This should even handles end of year roll overs correctly.

--

 

[tdatgod]

Hi,
I also came up with this SQL but it is teradata specific...

sel ((((ADD_MONTHS(date,1))(char(7))) || '/01') (date)) (named firstofnextmonth);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

firstofnextmonth
----------------
01/09/01


For some reason ADD_MONTH defaulted the display to YYYY-MM so I needed to up the cast to CHAR(7) from the previuos char(5).