Q: Can you use a batch SQL or store procedure to calculating the number of days in a month?
A:
Solution 1: Use CTE for clear structure
With Arg0 As
(
-- Calculate the first day of the month
Select Cast (
Cast ( Year ( Getdate ()) As Varchar ) + ' - ' + Cast ( Month ( Getdate ()) As Varchar ) + ' -01 '
As Datetime ) As Nowdt
),
Arg1 As
(
-- Calculate the first day of next month
Select Dateadd (Mm, 1 , Nowdt) As DT From Arg0
),
Arg2 As
(
-- Calculate the value of 1 minus the next month, that is, the last day of the month.
Select Dateadd (DD, - 1 , DT) As DT From Arg1
),
Arg3 As
(
-- Days
Select Datepart (DD, DT) As Daies From Arg2
)
Select * From Arg3
Solution 2: Use Variables
Declare @ Now Datetime
Set @ Now = Cast (
Cast ( Year ( Getdate ()) As Varchar ) + ' - ' + Cast ( Month ( Getdate ()) As Varchar ) + ' -01 '
As Datetime )
Declare @ Day Int
Set @ Day = Datepart (DD, Dateadd (DD, - 1 , Dateadd (Mm, 1 , @ Now )))
Select @ Day
Solution 3: single statement
Select Datepart (DD, Dateadd (DD, - 1 , Dateadd (Mm, 1 , Cast ( Cast ( Year ( Getdate ()) As Varchar ) + ' - ' + Cast ( Month ( Getdate ()) As Varchar ) + ' -01 ' As Datetime ))));