Wednesday, May 30, 2007

Select Statement for Get Number of Days in a Month

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).

19 comments:

Vishnu said...

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

Emil Chang said...

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

Vishnu said...

Hi !

Yes I does work well. Thank u so much.

Vishnu

Emil Chang said...

hi,
You are welcome.

Vishnu said...

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

Emil Chang said...

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

Vishnu said...

Hi !

Thank u so much. I will certainly get there and find out.

Thanks a lot.
Vishnu

Charlotte said...

Does anyone have handy the statement needed to subtract a datetime field from current time (getdate()) and display the value?

Dallas 101 said...

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.

Siddharth Mishra said...
This comment has been removed by the author.
Siddharth Mishra said...

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/

BADri said...

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

vipul said...

Thanks a lot sunny.

vipul said...

Thanks a lot Sunny for sulotion

shwetha said...
This comment has been removed by the author.
shwetha said...
This comment has been removed by the author.
shwetha said...

Can i select perticular month days using this query?
For Ex: I want all January month days is this possible?
like 1-tueday
2-WedDay
3-ThuDay
.....
In this form

Arun Vasu said...

http://myfrnz.tk/ to see the exact solution for getting days in a month using user define function in mssql

;;;; said...

I want number of working days in a month by removing Sundays from that month.