SQL : trying to find number of days in current month

Question

I have the following top part of my SQL. I am trying to pull out the current number 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 query is what I am trying to accomplish.

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

Does this answer your question? [How to determine the number of days in a month in SQL Server?](https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) — Jesusbrother, Jun 03 '22 at 04:21
I get the following error since there is no date in the data set and I am assigning GETDATE() as the date to add in. "syntax error at or near "@" — LaurenP, Jun 03 '22 at 04:27
@Jesusbrother, the upper part which you have shared seems to be fine, can you run just the select which you have shared excluding the T1.costcenter_id column? — PankajSanwal, Jun 03 '22 at 04:32
@PankajSanwal yep, here's no error occurring: https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=dd0402f61479e7f684e6fad83d8cecd5 — Jesusbrother, Jun 03 '22 at 04:35
@Jesusbrother, you must identify the problematic part in your select statement first and edit the question accordingly. — PankajSanwal, Jun 03 '22 at 04:38
@LaurenP can you make a dbfiddle with an example? It looks like you have hidden part of the code that caused this issue — Jesusbrother, Jun 03 '22 at 04:38
@PankajSanwal I don't think I should. This is not my post and not my statement in it, so I don't have a problem ) Better to ask topic starter — Jesusbrother, Jun 03 '22 at 04:41
Yes, please refer here to the SQL it's almost 1000 lines but the majority of the query I named as T1 and pulling from there . https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=20fcc7f3351e2cbb7222dd8a301a53dc — LaurenP, Jun 03 '22 at 04:43

Jesusbrother · Accepted Answer · 2022-06-03T04:57:52.677

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).

SQL : trying to find number of days in current month

1 Answers1