Query for monthly reports and bissextile years

Classic Servoy
1

I used the method present in CRM solution for monthy report (created a stored calc which return the month number and searched that field using a SQL query).
But, I’m just curious, what would be the best way to do everything in SQL, using a query like …WHERE date BETWEEN 1-2-2004 AND 29-2-2004?
In FileMaker I used a series of Ifs and, in the case of february, I used to write something like:

gMonth = "february" and  Mod(gYear ; 400) = 0 or gMonth = "february" and  Mod(gYear ; 4) = 0; "1/2/" & gYear & "...29/2/" & gYear;

Is this the best way in Servoy too? I tried to combine a SWITCH statement (jan, feb, mar etc…) with an IF only in february, but I had problems writing the code.
Does anybody has a suggestion?

Thanks

2

Dunno if this would work for you, 'cause I do not know what DB you’re working on and if every SQL variant offer this…

If you would do a group by on the datefield, where you format the datefield as only months would do the trick…

In Oracle SQL it would look like:

select field1, startdate from table1 gp by to_char(startdate,‘MM’)

Hope this helps you a bit further…

Paul

3

pbakker:
Dunno if this would work for you, 'cause I do not know what DB you’re working on and if every SQL variant offer this…

If you would do a group by on the datefield, where you format the datefield as only months would do the trick…

In Oracle SQL it would look like:

select field1, startdate from table1 gp by to_char(startdate,‘MM’)

Hope this helps you a bit further…

Paul

Thanks for the suggestion, Paul

but I need to send a SQL-standard query, 'cause my solution has to be db-independent.
So, the query should use the BETWEEN statement or combine “>” start_date and “<” end_date.
I started writing a method like:

 switch( monthchoice )  
 {  
 M-  case "january":
	var date_interval = "'" + current_year + "-1-1' AND '" + current_year + "-1-31'"
	break;
 case "febbraio":
	"????"
 M-  M- default:  
 M-  M-  M-  break;  
 }

Inside the february step, I tried to put an If condition (if it’s bissextile, return “'” + current_year + “-2-1’ AND '” + current_year + “-2-29”
else return
“'” + current_year + “-2-1’ AND '” + current_year + “-2-28”

But I couldn’t write the right code…

4

Ciao Riccardino

this one may help

var last_day_feb = new Date(Date.UTC(your_year,2,1) - 865E5)

in last_day_feb you will find the last day of the february of your_year

865E5 is the number of milliseconds in one day (86,500,000)

Date.UTC(your_year,2,1) is the number of milliseconds of your years’s 1th of march since 1/1/1970

5

Salve Riccardino

I think you can change all your switch (monthchoice) block with

var chedata = new Date()  // month/year you are looking for
var anno = chedata.getFullYear() 
var mese = chedata.getMonth()
var start_day = new Date (anno,mese,1) // first day of month
var end_day = new Date(Date.UTC(anno,mese+1,1) - 865E5) // last day of month
var date_interval = "'" + utils.dateFormat(start_day,"MM-dd-yyyy") + "' AND '" + utils.dateFormat(end_day,"MM-dd-yyyy") + "'"

I tested and seems to work… also for december… :wink:
hope this helps

ciao

Enrico