Solved

Getting Current Monthend

Posted on 2008-06-20
6
195 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
sorry but I think my date is a string '20080429'
0
 

Author Comment

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

it returns

2008-05-28
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 142

Accepted Solution

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

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now