CODEWITHSUNDEEP
mysqlsqldate
user1414880
user1414880

Reputation:

how to find out number of days in month in mysql

I have several dates in mysql tables, using those dates I need to find out the number of days in the month. Suppose i have 2003-02-05 it should return 28. for example

date                days_in_month
2003-2-3            28

Upvotes: 39

Views: 50051

Answers (7)

Saranya Jothiprakasam
Saranya Jothiprakasam

Reputation: 294

Try

select DAY(LAST_DAY(yourdate)) as days_in_month

Upvotes: 3

Krishnakumar
Krishnakumar

Reputation: 11

I think you are asking the total number of days to be returned for a month. If you are trying to find the total number of days for current month, here is the query:

select timestampdiff(day,
concat(year(now()),'-',month(now()),'-01'),
date_add( concat(year(now()),'-',month(now()),'-01'), interval 1 month)).

If you want to externalize this using any programming language, externalize year and month in the code and replace that with now()

Upvotes: 1

Fedor Hajdu
Fedor Hajdu

Reputation: 4697

SELECT DAY(LAST_DAY(yourdate))

Upvotes: 97

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Use following statement

SELECT DAY(LAST_DAY(now()))

Upvotes: 2

Cylindric
Cylindric

Reputation: 5894

An alternative to string-chopping is to use:

SELECT DAY(LAST_DAY('2010-02-1'));

Upvotes: 2

sumit
sumit

Reputation: 15464

You can combine LAST_DAY with string function

SELECT RIGHT( LAST_DAY(  '2003-02-03' ) , 2 )

Upvotes: 3

Marco
Marco

Reputation: 57573

Try this:

SELECT DAYOFMONTH(LAST_DAY(your_date)) FROM your_table

Upvotes: 3

Related Questions