Month to Date and Year to Date in DB

[Mansenie]

Running into issues using in DB tools. I need to get the month to date average and the year to date average using in DB. I have tried to use (datetimeadd and datetimefirstofmonth) but it did not work using Formula in DB. Are there any work arounds i am not thinking of ?

[BrandonB]

You can use a formula to set each date to the first of that month and then summarize in dB and group by that date field in order to get the monthly value and then apply the same logic to get the yearly.

[Mansenie]

Right that is what i am trying to do but i can't figure out the syntax to get that first of the month or first of the year.

[danilang]

Hi @Mansenie 

The syntax that you use in-DB executes on the server and is dependent on the specific database you're connected to.  Which one are you using, Oracle, SQL server, Teradata, etc?

Dan   

[Mansenie]

I am using teradata and IBM DB2.

[danilang · Accepted solution]

Hi @Mansenie 

Teradata uses Extract to return the month and year as integers

SELECT EXTRACT(YEAR FROM CURRENT_DATE);  
EXTRACT(YEAR FROM Date) 
----------------------- 
        2016  
SELECT EXTRACT(MONTH FROM CURRENT_DATE);  
EXTRACT(MONTH FROM Date) 
------------------------ 
          1        

Dan