Using SQL statement or SQL Query to calculating the Number of Days in a Month
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
Example on may(month), you just copy and paste the SQL Query or SQL statement (Above blue color) to the SQL Query Analyzer. You will get the answer is 31 days for May(month).
14 comments:
Can the same query be modified to suit MySql. I tried but the same failed, perhaps due to syntax error. I would be delighted to have the MySql query for the same.
Thanks
SELECT DAY(LAST_DAY(now()))
SQL Statement Above is for MySql Database. I tested MySql Server 5.0, it work for me...
For more information, please visit below link
MySQL Date Function
Hi !
Yes I does work well. Thank u so much.
Vishnu
hi,
You are welcome.
Hi !
Actually i want to write a MySql query that would return exactly the number of working days in a month excluding Saturdays and Sundays grouped by month so that i can calculate the Total Available Working Hours month wise.
Can this be achieved in a select query.
Vishnu
I have done in and u can view the solution at below link. but i do it in Ms SQL Server.
http://sqltutorials.blogspot.com/2007/06/sql-statement-get-total-working-day-in.html
Hi !
Thank u so much. I will certainly get there and find out.
Thanks a lot.
Vishnu
Does anyone have handy the statement needed to subtract a datetime field from current time (getdate()) and display the value?
In Sql here is a good site
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=5126
For MS Access you can use the
Date() - 1 Where 1 is the number of days to be subtracted.
You can use another version of Select query to get number of days in a particular Month :-
Select DATENAME(DAY,DATEADD(DAY,-1,DATEADD(Month,1,'February 2009')))
Above query will give 28 as result.
Please try it.
You can put date in "MM/DD/YYYY" or "MM/DD/YY" format also.
Thanks to my Colleague "Amit Pawar" for this solution.
Siddharth,
http://siddharthmishrajob.blogspot.com/
without using any variable: Calculate no of days in a Month:
select datediff(dd,dateadd(dd, -day(getdate())+1,getdate()), dateadd(m,1,dateadd(dd, -day(getdate())+1,getdate())))
thanks
Sunny.L
Thanks a lot sunny.
Thanks a lot Sunny for sulotion
Post a Comment