Reputation: 29
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
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
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
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