Community
Enterprise Resource Planning Blog Posts by Members
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Working with Date Range in sql query

gianluca_calloni
Participant
‎2013 Feb 05 4:26 PM
3,480

Hi to all. I write this little blog post to share with you how set a query to retrieve turnover setting up the date range in dinamyc way..

I try to explain better the situation.

The C.E.O. would like to have the turnover in this way:

  1. the montant turnover from the first of january until the end of the previous month, for this year and the previous year, in 2 columns.
  2. the turnover from the first till the end of previous month, for current year and previous year.
  3. The turnover for the current month, for the current year and the previous year

well.. supposing query run manually, when CEO run query from query management, i use the following SQL code to set dates in properly way:

If today is the february, 04 2013, the current_timestamp return 04-02-2013 15:18:43.852, with the datediff and dateadd sql commands, we can find all dates that we need.

To find the:

  • first of January, the first day of the current year (point 1), we can use
    • dateadd(month, datediff(month, 0,@date)-month(@date)+1,0)
  • the last day of the current year we can use
    • dateadd(month,datediff(month,0,@date)+13-month(@date),-1)
  • the first day of previous year
    • dateadd(year,-1,dateadd(month, datediff(month, 0,@date)-month(@date)+1,0))
  • the last day of previous year
    • dateadd(year,-1,dateadd(month,datediff(month,0,@date)+13-month(@date),-1))
  • the first day of previous month, current year
    • dateadd(month,datediff(month,0,@date)-1,0)
  • the last day of previous month current year
    • dateadd(month,datediff(month,0,@date),-1)
  • the first day of previous month, previous year
    • dateadd(year,-1,dateadd(month,datediff(month,0,@date)-1,0))
  • the last day of previous month, previous year
    • dateadd(year,-1,dateadd(month,datediff(month,0,@date),-1))

Note that we use the datediff command cause it return date in dd-mm-yyyy 00:00:00.000. If we use only the dateadd command, the result is dd-mm-yyy 15:18:43.852, and this could be a problem if some document (invoice) in recorded in the database at the 01-02-2013 09:15:36.963.

in fact if we run the query after 09:15:36.963 hour, the invoice is not included in the query.

Have a nice day