Replace the date value to first of the month if it is any other day

Question

I have a requirement for a date column.
Condition: If the date column value is any date other then the 1st of its month then the same has to be replaced to 1st of that month, say for example today its 05-01-2017 and it has to be replaced by 01-01-2017. Similarly for 15th of the month.

I have achieved the same using below query:

select 'date column',
    case when datediff(DAY,-15, 'date column') != 41043 then 
             DATEADD(dd,-(DAY( 'date column')-1), 'date column')
         end 
from TABLE

This I cracked by running below query:

select datediff(DAY,-15,'date column') 
from TABLE

This gives value "41043", which I used in my query.

But I have two concerns here

what is this value "41043" ?? like is it ASCII value of that date or subtraction of date from SQL beginning date ..etc..
Is there any other Simpler way to achieve my query?

Please suggest.

-15 convert to `datetime` is 1899-12-17, thus datediff result 41043 comes from '2012-05-01' — Eric, Jan 05 '17 at 07:13
Thanks eric. Is there any other dynamic way to handle this as i have to use the value every time? — Nischey Anand, Jan 05 '17 at 08:01

score 2 · Answer 1 · edited May 23 '17 at 12:33

2

There is also an easier way without using CASE, you can always add 16 days (for 31 days months) then found first day of the month like this:

SELECT 
    DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, 16, dateColumn)), 0) AS firstDayOfMonth
FROM
    yourTable;

edited May 23 '17 at 12:33

Community

1
1

answered Jan 05 '17 at 09:11

shA.t

15,880
5
49
104

Juozas · Accepted Answer · 2017-01-05T08:23:53.303

1

SELECT
    'date column'
    ,CASE
        WHEN  DATEPART(DAY, 'date column') < 15 THEN 
            CONVERT(VARCHAR(10), DATEADD(mm, DATEDIFF(mm, 0, 'date column'), 0), 105)
        WHEN  DATEPART(DAY, 'date column') >= 15 THEN
            CONVERT(VARCHAR(10), DATEADD(mm, DATEDIFF(mm, 0, 'date column'), 14), 105) 
     END
FROM    
    [TABLE]

edited Jan 05 '17 at 08:23

answered Jan 05 '17 at 08:00

Juozas

885
10
16

Hi Juozas, This just converstion part, i have to check the condition as well. This has to be updated only when it satisfies the condition. – Nischey Anand Jan 05 '17 at 08:07
I have updated query, please try. Execuse me, if I incorrectly understood the question :) Now it works in following way: 1st day, if dt < 15, 15, if day >= 15 – Juozas Jan 05 '17 at 08:24
Hi Juozas, Thanks .it works with small corrections. Thanks for logic.I was solving it in harder way :-p – Nischey Anand Jan 05 '17 at 09:05

Replace the date value to first of the month if it is any other day

2 Answers2