Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date.
I found on some blog sites to use this but it will not work for every date
DECLARE @date DATETIME SET @date = '05/17/2020' SELECT DATEDIFF(Day, @date, DATEADD(Month, 1, @date))
Try to use date '1/31/2013' or '3/31/2013' or '5/31/2013'
For 1/31 it will give 28 and for '3/31/2013' and '5/31/2013' it will return 30 which is wrong, so we will use the correct one like this
DECLARE @date DATETIME SET @date = '05/17/2020' SELECT DATEDIFF(Day, DATEADD(day, 1 - Day(@date), @date), DATEADD(Month, 1, DATEADD(Day, 1 - Day(@date), @date)))
Try it with above dates or any other date you want to check and it will always give correct result.
In SQL Server 2012 a new datetime function is introduced (actually 7 new datetime fucntions introduced) named EOMONTH which return last date of month so we can also use this to get the number of days in a month
Let's see first EOMONTH
SELECT EOMONTH(GETDATE()) LastDayofMonth
Result: 2013-05-31 00:00:00.000, so simply we can get days from this result so use like this
DECLARE @date DATETIME SET @date = '1/31/2013' SELECT DAY(EOMONTH(@date)) AS DaysInMonth
Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
|By Hamden On 14 May, 13 Viewed: 432|
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 619
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 676
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 1,782
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 458
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 8,502