How to Select Last Date of a Months

Question

0.00/5 (No votes)

See more:

CSS

2012-06-22 00:00:00.000 577169.990000
2012-06-30 00:00:00.000 794.990000
2012-06-30 00:00:00.000 256154.990000
2012-06-30 00:00:00.000 295904.990000------ i want this line.
2012-07-05 00:00:00.000 1096469.980000
2012-07-10 00:00:00.000 2384.980000
2012-07-28 00:00:00.000 161384.970000
2012-07-28 00:00:00.000 368084.960000
2012-07-28 00:00:00.000 1085729.960000
2012-07-28 00:00:00.000 1228829.960000------ i want this line.
2012-08-02 00:00:00.000 1341719.970000
2012-08-02 00:00:00.000 541719.970000
2012-08-07 00:00:00.000 1341719.970000
2012-08-07 00:00:00.000 961384.970000
2012-08-27 00:00:00.000 161384.970000------ i want this line.
2012-09-10 00:00:00.000 2384.970000
2012-09-25 00:00:00.000 619290.570000
2012-09-27 00:00:00.000 3790.570000
2012-09-29 00:00:00.000 84790.560000
2012-09-29 00:00:00.000 441190.550000------ and want this line.

how can it be done..

i have tried

DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, '2012-06-01') + 1, 0))

but it select only all the last dates like

CSS

2012-06-30 00:00:00.000 794.990000
2012-06-30 00:00:00.000 256154.990000
2012-06-30 00:00:00.000 295904.990000

Posted 10-Dec-13 21:26pm

xibit89

Add a Solution

Comments

Peter Leow 11-Dec-13 3:35am

You want the last record of every month, right? But you need time element to determine that especially if there are multiple records on the last date of a month.

xibit89 11-Dec-13 3:36am

yep i need the last record of every month. So, how could i determine it?

2 solutions

Solution 3

Hello ,

try this

SQL

--first declare a given date
declare @date date

--declare  day difference of first and last day of a month as int
declare @daydifference int


--declare a lastday as date

declare @lastday date


--set a given date in the variable

set @date='2016-2-12'


--get the daydifference  of first and last day

set @daydifference=(select datediff(day, dateadd(day, 1-day(@date), @date),
              dateadd(month, 1, dateadd(day, 1-day(@date), @date))))


--get the firsdtday of a month

DECLARE @firstDayOfMonth date = CAST( CAST(YEAR(@date) AS varchar(4)) + '-' + 
CAST(MONTH(@date) AS varchar(2)) + '-01' AS date)    
                                  

--now get the lastday of a month

set @lastday=(select DATEADD(DAY, @daydifference-1,@firstDayOfMonth))
 select @lastday

it will show '2016-2-29' as output
thanks

Posted 10-Dec-13 22:37pm

Animesh Datta

Updated 11-Dec-13 4:44am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BK 4 code · Accepted Answer · 2013-12-10T22:06:00

Solution 2

You could use this query.

Select datetime,value from Table group by year(datetime),month(datetime) having datetime=max(datettime)

Posted 10-Dec-13 22:06pm

BK 4 code