Solved

Getting Current Monthend

Posted on 2008-06-20
6
228 Views
Last Modified: 2010-04-21
Hi all,
Does anyone know how to calculate current monthend date from a date..

so 29-04-2008
returns
30-04-2008

Thanks!
Micky
0
Comment
Question by:MickeyMin
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21829580
this should do:
select dateadd(day, -1,dateadd(month, 1,convert(datetime,convert(varchar(8),  your_date_field , 120) + '01',120)))

Open in new window

0
 

Author Comment

by:MickeyMin
ID: 21829606
sorry but I think my date is a string '20080429'
0
 

Author Comment

by:MickeyMin
ID: 21829612
if I just run this part  select dateadd(day, -1,dateadd(month, 1,'20080429'))

it returns

2008-05-28
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21829616
no problem:
select dateadd(day, -1,dateadd(month, 1,convert(datetime,left('20080429',6) + '01',112)))

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21829619
note: if you want to get a "date range" query to include up to the last day of the month, it should best be something like this, doing a < of the first of next month:
select ...
where yourfield < dateadd(month, 1,convert(datetime,left('20080429',6) + '01',112))

Open in new window

0
 

Author Closing Comment

by:MickeyMin
ID: 31469096
thank you!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 26
Are triggers slow? 7 13
sql 2016 data tools breakdown.. 1 15
MS SQL + date 6 23
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question