First day of current month date passed

Transact-SQL
1

Hi Experts
I am trying to get the first day of the month if any date passed e.g '9/26/2017' the query should give the date first date of the month 9/1/2017 i am unable to figure out the date coming in the result is 8/1/2017

SELECT otr.ITEM_NAME,
        otr.START_DATETIME Date1,
        DATEADD(mm,DATEDIFF(mm,0,otr.START_DATETIME),0) Firstday, --- First day of the month,
        --DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,otr.START_DATETIME)+1,0)) Lastday, ---last day of the month,
        otr.PROD_OIL_VOL prod_oil,
        otr.ITEM_ID,
        otr.ITEM_TYPE  
FROM tableotr
WHERE otr.ITEM_NAME IN ('SM')   
    AND otr.ITEM_TYPE='type_1'  AND  otr.START_DATETIME >= DATEADD(mm,DATEDIFF(mm,0,otr.START_DATETIME),0) AND  otr.START_DATETIME <= '9/26/2017' 
    AND otr.PROD_OIL_VOL=
                         (SELECT MAX(PROD_OIL_VOL) FROM table_test 
                          WHERE  ITEM_NAME IN ('SM')   AND ITEM_TYPE='type_1' 
                                 AND otr.ITEM_ID = ITEM_ID  
                                 AND  START_DATETIME >= DATEADD(mm,DATEDIFF(mm,0,START_DATETIME),0)AND  START_DATETIME <= '9/26/2017' ) 
                                 AND ITEM_TYPE='type_1'

Unable to upload the picture...

**Results **
ITEM_NAME Date1 Firstday prod_oil ITEM_ID
SM 2017-08-01 00:00:00.000 2017-08-01 00:00:00.000 274768.05 d5e7ca891ca74a8cbb0297f9da3dadf6

2

Your code (replacing the hazy "mm" with the clear "MONTH"):

DATEADD(MONTH,DATEDIFF(MONTH,0,otr.START_DATETIME),0)

will always return the first day of that month.

The otr.START_DATETIME must be in August.

The problem is in the WHERE clause; you'll want to use CAST(GETDATE() AS date) rather than START_DATETIME in it.

3

By changing

DATEADD(mm,DATEDIFF(mm,0,otr.START_DATETIME),0)

To
DATEADD(MONTH,DATEDIFF(MONTH,0,otr.START_DATETIME),0)

Still coming First date of August not 1st date of September ?

4

As I said before, that's because the start_datetime is in AUGUST.

You need to re-read what I wrote above about changing the WHERE clause to correct the problem.

5

If i understand well like that below ?

SELECT otr.ITEM_NAME,
        otr.START_DATETIME Date1,
        DATEADD(mm,DATEDIFF(mm,0,otr.START_DATETIME),0) Firstday, --- First day of the month,
        --DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,otr.START_DATETIME)+1,0)) Lastday, ---last day of the month,
        otr.PROD_OIL_VOL prod_oil,
        otr.ITEM_ID,
        otr.ITEM_TYPE  
FROM table_test  otr
WHERE otr.ITEM_NAME IN ('SM')   
    AND otr.ITEM_TYPE='type_1'  AND  CAST(otr.START_DATETIME AS date) >= DATEADD(MONTH,DATEDIFF(MONTH,0,otr.START_DATETIME),0) AND  CAST(otr.START_DATETIME AS date) <= '9/26/2017' 
                         (SELECT MAX(PROD_OIL_VOL) FROM table_test 
                          WHERE  ITEM_NAME IN ('SM')   AND ITEM_TYPE='type_1' 
                                 AND otr.ITEM_ID = ITEM_ID  
                                  AND  CAST(START_DATETIME AS date) >= DATEADD(MONTH,DATEDIFF(mm,0,START_DATETIME),0)AND  CAST(START_DATETIME AS date) <= '9/26/2017' ) 
                                 AND ITEM_TYPE='type_1'
6

When you use this DATEADD(MONTH,DATEDIFF(MONTH,0,otr.START_DATETIME),0) in your WHERE clause you are telling SQL to include that row if the otr.START_DATETIME is greater than the 1st of the month for the otr.START_DATETIME column.

In other words - ALL ROWS will qualify on that statement...if otr.START_DATETIME is 2014-06-22 then the first of that is 2014-06-01.

To insure that you are only looking at the current month:

WHERE otr.START_DATETIME >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- First of THIS month
AND otr.START_DATETIME < CAST(GETDATE() AS DATE) -- less than midnight TODAY

7

Did the same as mentioned still having the same results showing date 8/1/2017 instead of 9/1/2017.( the last row is placed on 9/26/2017 and current date is 9/26/2017 right now)

Where clause

AND  otr.START_DATETIME  >= DATEADD(MONTH, DATEDIFF(MONTH, 0, otr.START_DATETIME ), 0) 
AND otr.START_DATETIME < CAST(GETDATE()  AS DATE)

Sub Query

AND START_DATETIME >= DATEADD(MONTH, DATEDIFF(MONTH, 0, START_DATETIME ), 0) AND START_DATETIME < CAST(GETDATE() AS DATE)

8

Re-read what I posted and compare to what you are using...

You still have the column in the calculation for the first of the month. That calculation will give you the first of the month of the start date, not the first of this month.

When you include a row with a start date in August, the results will return the first of August.

9

Thank you jeffw8713 and thanks ScottPletcher i got it now works for me .