SQl-Trying to find number of days in current month-CodePudding

I have the following top part of my sql .I am trying to pull out the current amount of days in a month. When I run this I get the error "Invalid operation: cannot cast type integer to timestamp without time zone;" How to I update this so it will pull the number of days in the current month.

SELECT T1.costcenter_id,
    DATEPART(MONTH, GETDATE())as "present month",
    DATEPART(DAY, GETDATE())as "present day",
    DAY(EOMONTH(GETDATE())) as "days in month",

I used this as a temporary option

CASE 
    WHEN DATEPART(MONTH, GETDATE()) = 1 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 2 THEN 28
    WHEN DATEPART(MONTH, GETDATE()) = 3 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 4 THEN 30
    WHEN DATEPART(MONTH, GETDATE()) = 5 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 6 THEN 30
    WHEN DATEPART(MONTH, GETDATE()) = 7 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 8 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 9 THEN 30
    WHEN DATEPART(MONTH, GETDATE()) = 10 THEN 31
    WHEN DATEPART(MONTH, GETDATE()) = 11 THEN 30
    WHEN DATEPART(MONTH, GETDATE()) = 12 THEN 31
    END AS days_in_month

the ultimate goal is to get the percent completion of the month so if the 3rd day of June there is still 93% of the month left. The below querey is what i am trying to acccomplish.

1-(DATEPART(DAY, GETDATE())/ total days in the month) as % complete

CodePudding user response：

Well, the dbfiddle that you provide not working properly, so I have no possibility to fix it without some data. Thus I can answer only on your main question. Here's example how to get last day and percentage of complete:

WITH t as (
SELECT
    DATEPART(MONTH, GETDATE())as present_month,
    DATEPART(DAY, GETDATE())as present_day,
    DAY(EOMONTH(GETDATE())) as days_in_month)
select t.*,
cast(present_day as decimal) / days_in_month * 100 as '% complete'
from t;

Here's dbfiddle example, to check that no error here (pay attention to the conversion to decimal or numeric before performing the division operation, due to specific of integer dividing).