0

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

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

Please suggest.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Nischey D
  • 189
  • 3
  • 18
  • -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 D Jan 05 '17 at 08:01

2 Answers2

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;
Community
  • 1
  • 1
shA.t
  • 16,580
  • 5
  • 54
  • 111
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]
Juozas
  • 916
  • 10
  • 17
  • 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 D 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 D Jan 05 '17 at 09:05