1

Suppose there is one date in int format 20191229, I want to find end of month and check if it's end of month is of 31 days or 30 days in SQL Server

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sumit Tiwary
  • 29
  • 1
  • 1
  • 9
  • 5
    Why are you storing dates as an `int`? You're should really be using the date and time data types; it's what they're for. – Thom A Dec 29 '19 at 09:17
  • I will add from myself. `DATE` data type takes less disk space. It requires 3 bytes, while `INT` data type requires 4 bytes, `DATETIME` data type - 8 bytes. https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15 – Max Zolotenko Dec 29 '19 at 09:52
  • If your version of SQL Server does not have the `EOFMONTH` function the see [this](https://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month) answer. (Function was added in 2016) – Peter Smith Dec 29 '19 at 10:04
  • `EOMONTH` is available in every **supported** version of SQL Server, @PeterSmith . I *think* it was added with th 2012, but it might have been 2008. – Thom A Dec 29 '19 at 10:17
  • @Lamu. I checked the Microsoft documentation before posting the comment. It's definitely not in 2008R2 and the reference says not in 2014. Is there a definitive source as to when features were introduced. – Peter Smith Dec 29 '19 at 10:49
  • It's definitely in 2012+ @PeterSmith as the office still has 2012 instances and I use it. Quick Google found me this too: https://blogs.msdn.microsoft.com/samlester/2013/09/23/eomonth-equivalent-in-sql-server-2008-r2-and-below/ – Thom A Dec 29 '19 at 11:18
  • Larnu In my table data is already stored in integer format. I just want to get last date of month by converting it to a specific format. After I get last day of month I want to compare it in if statement like..if(datepart(dd,eomonth(column)))='31' but the problem is column is in integer format. – Sumit Tiwary Dec 29 '19 at 12:34
  • Yes, so I repeat by question in my original comment *"Why are you storing dates as an `int`? You're should really be using the date and time data types; it's what they're for."* You're using the **wrong** data type. Fix the data type, fix the problem. – Thom A Dec 29 '19 at 13:54

3 Answers3

3

You can try this from the reference. The given answer will not work for the integer data type but it will work in the varchar datatype date value. Storing Date values in integer is not a good idea, so as suggested by Larnu change the data type in either date or varchar.

SELECT 
   Day(EOMONTH(Cast('20191229' as Date))) end_of_month;
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • No, because integer can not be converted to date. But it will work with varchar. Here is an example https://rextester.com/XAQM47817 – Suraj Kumar Dec 29 '19 at 09:30
  • 1
    You can also do something like this: `SELECT DAY(EOMONTH(CAST(CAST(20191229 AS VARCHAR(10))AS DATE))) END_OF_MONTH;` – Max Zolotenko Dec 29 '19 at 09:36
  • @МаксимЗолотенко Yes. – Suraj Kumar Dec 29 '19 at 09:38
  • Just noting that I did *not* recommend to use a `varchar`. Only the Date and Time data types are the correct data types for a date/time. – Thom A Dec 29 '19 at 17:05
  • This option doesn't work in Sql Server 2008 R2 or SQL Server 2005. Showing below error [ Msg 195, Level 15, State 10, Line 2 'EOMONTH' is not a recognized built-in function name. ] – Saket Yadav Dec 30 '19 at 08:40
0

If you want the amount of days within a month, as you need the days as an integer, you should go for this. This is the most robust built, but also the more complex one, as to make sure the Integer value is processed correctly:

SELECT DATEPART(DAY,EOMONTH(CAST(CAST('20191229' AS NCHAR(8)) AS DATE))) AS Days

Result:

Days
31
Atmira
  • 249
  • 1
  • 4
  • 18
  • I want to use if condition here like if(datepart (dd,eomonth(column))='31' ) and same statement like you but when I try to put column name instead of date I get error like invalid column. – Sumit Tiwary Dec 29 '19 at 12:40
  • Why do you need to cast a string literal to varchar(10)? And why does that cast need to be varchar and not char? And why 10 characters and not 8? Sloppy code! – SMor Dec 29 '19 at 13:19
  • @SMor sorry for being a bit sloppy, it has been updated now. Thought it would be understood anyhow. A nicer tone instead of just ranting would be appreciated - or even better, feel free to edit next time. – Atmira Dec 29 '19 at 19:18
  • Atmira thanks for the help it worked i just used 1 extra variable to store this and used that variable in my if statement: IF(DATEPART(day, EOMONTH(@MINdate)) = 31) – Sumit Tiwary Dec 30 '19 at 11:43
0

If you want to add an IF evaluation to your selected date(s), you can do this by add an IIF-clause where it evaluates whether or not the end of month is 31 or not. If you want to use a columnname instead of the date, just substitute @Date with the columnname. I've just added the variable @Date instead of '20191229' to make it more illustrative/understandable. You can change the True/false descriptions to whatever you like in the query.

DECLARE @Date AS int
SET @Date = '20191229'

SELECT 
IIF (
    DATEPART(DAY,EOMONTH(CAST(CAST(@Date AS NCHAR(8)) AS DATE))) = '31'
    ,'True'
    ,'False'
    ) AS Days

Output:

Days
True
Atmira
  • 249
  • 1
  • 4
  • 18