CODEWITHSUNDEEP
sqlsql-server
Sumit Tiwary
Sumit Tiwary

Reputation: 29

SQL Server end of month

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

Upvotes: 1

Views: 1227

Answers (3)

Atmira
Atmira

Reputation: 249

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

Upvotes: 0

Atmira
Atmira

Reputation: 249

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

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

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;

Upvotes: 3

Related Questions