Hi Chris and Jeff thank you for the replies. i will hold these values in temp table and will be using it for further use in joining with other tables. those first and last date of month will be required for other operations.
Hi Chris, i got the below error. I am usingg sqlserver 2008R2
Msg 195, Level 15, State 10, Line 7
'EOMONTH' is not a recognized built-in function name
EOMONTH was added in SQL 2012. Chris' formula will also produce incorrect results whenever the current month does NOT have 31 days. Try making the following modification to his code.
SELECT bom = DATEADD(day,1,DATEADD(month,-1,x.eom)), x.eom
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT eom = DATEADD(MONTH,DATEDIFF(MONTH, -1, GETDATE()) - n, -1)) x
Drew
Cheers Drew - but there's an error in your version too:
DECLARE @getdate-2 DATETIME = '20170228'-- Drew's code
SELECT bom = DATEADD(day,1,DATEADD(month,-1,x.eom)), x.eom
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT eom = DATEADD(MONTH,DATEDIFF(MONTH, -1, @getdate-2) - n, -1)) x -- Chris v2
SELECT bom, eom = DATEADD(DAY,-1,DATEADD(MONTH,1,bom))
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT bom = DATEADD(MONTH,DATEDIFF(MONTH,0,@GetDate) - n,0)) x