SQLTeam.com | Weblogs | Forums

SQL Syntax help for dates


#1

I'm currently using the following for data trends for the last 12 months. However, I need to not include the current month (i.e. August now) and just the last 12 months through last month (July). Any ideas on how I can modify this to have August excluded?

"Project Create Date"."Project Create Date" > timestampadd(sql_tsi_month,-12,CURRENT_DATE)


#2

Use [Project Create Date] < DATEADD(month, DATEDIFF(month, 0, CURRENT_DATE), 0)


#3

Is there another method for this? I'm getting an error on the one above.


#4

Try replacing "CURRENT_DATE" with a good value (I used GETDATE()).


#5

"timestampadd" suggests you are using something other than Microsoft SQL Server?

This is a Microsoft SQL Server forum, so it might be that there is no=one here familiar with the flavour of SQL that you are using - assuming you are not using MS SQL telling us what it is will establish if anyone here thinks they can help or not!


#6

My apologies on that. I am actually working on SQL syntax in Oracle OBEE


#7

Dunno if the "concept" of how we do it in MS SQL will help, as DJ55 posted earlier

DATEDIFF(month, 0, CURRENT_DATE)

is the number of months from "beginning of time" to today (result is the same whether Today is 1st or Last of the month)

DATEADD(month, EarlierResult, 0)

Then we add that earlier result to "Date 0" and we wind up with a DateTime (data type) which is the 1st-of-the-month.

We can then select LESS THAN (i.e. NOT INCLUDING EQUAL) to exclude the current month.

Dunno about Oracle, but in MS SQL it seems to work more easily to do

WHERE     MyColumn >= @MyStartDate
      AND MyColumn <  @OneMoreThanMyEndDate

rather than trying to use BETWEEN


#8

Exactly. @Kristen Wish I could write so well


#9

Thank you for the help to you and @Kristen :sunglasses:

I'll try and provide more information upfront next time as well.


#10

Since you do not bother with DDL for us, I will dimply say that you need a julianized ordinal month in your calendar table and let you figure it out. Why should we bother with DML? The worst way is to do temporal math with the proprietary temporal functions. Cannot optimize, cannot port and it is slow. .