Bookmark Topic Watch Topic
  • New Topic

sql syntax in a servlet  RSS feed

 
sam davis
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hello,
I'm developing a reporting servlet. IN that, i have a sql statement that shows all the fields of a table WHERE date > <dynamically generated to always use the first day of the current month>.
Could anyone help me out with the syntax to dynamically generate the first day of the current month???..ANy suggestion or any links where i can get more info on this would be greatly appreciated...THanks
 
Glynn Lau
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Report post to moderator
Maybe just like
where date > year(getdate())+month (getdate())+'01'
try again..
 
sam davis
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Report post to moderator
thats giving me rows for April!!!..any other ideas or suggestion..even Java code to get the first day of the current month would help..i can convert it into a string and use that string in my sql statement...Thanks a lot..
 
Andy Bowes
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Report post to moderator
In Java to calculate the first day of the current month:
GregorianCalendar calendar = new GregorianCalendar();
int currentDay = calendar.get(Calendar.DAY_OF_MONTH);
calendar.add(Calendar.DATE, -1 * ( currentDay - 1 ));
Date firstOfMonth = calendar.getTime();
If you are going to use this in SQL you might also need to set the hours, minutes, seconds etc to zero before getting the time from the calendar.
Hope this helps
 
saahil sinha
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
  • Report post to moderator
u can use the set() function defined by calendar.
GregorianCalendar cal=new Gregorian Calendar();
cal.set(Calendar.DAY_OF_MONTH,1);
That should hep you and rem to set hrs mins and sec as sql has a datetime datatype for dates.
Ciao
 
sam davis
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hello,
Thanks a lot for your help..i was able to convert it into a string and use it in my query. Appreciate it.
Sam
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Report post to moderator
Just realised this is a duplicate of the conversation here. I'm going to ask for this thread to be closed.
Sam, please don't post the same question in multiple forums, it wastes peoples time,
Thanks,
Dave
 
    Bookmark Topic Watch Topic
  • New Topic
Boost this thread!