• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Getting Current Monthend

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
MickeyMin
Asked:
MickeyMin
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MickeyMinAuthor Commented:
sorry but I think my date is a string '20080429'
0
 
MickeyMinAuthor Commented:
if I just run this part  select dateadd(day, -1,dateadd(month, 1,'20080429'))

it returns

2008-05-28
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no problem:
select dateadd(day, -1,dateadd(month, 1,convert(datetime,left('20080429',6) + '01',112)))

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MickeyMinAuthor Commented:
thank you!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now