Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000
LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000
LastDay_NextMonth
———————–
2007-09-30 23:59:59.000
If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000
Reference : Pinal Dave (https://blog.sqlauthority.com)
289 Comments. Leave new
i need to get latest records for a group of products,that is
latest time and updated quantity of each product
select * from table
where datecol=(select max(datecol) from table)
Thanks for your reply.
Your query would only return the record with latest date but not all records.
In my case there are different products and the product quantities are not updated on daily basis.
For eg.
I have 3 products ..
Updateddatetime Productname qtyupdated
2011-01-28 10:25:47.253 p1 10
2011-01-29 12:25:47.253 p1 20
2011-02-20 10:25:47.253 p2 10
2011-02-22 10:25:47.253 p2 90
2011-02-20 10:25:47.253 p3 50
2011-02-22 11:25:47.253 p3 60
I should get the result as
Updateddatetime Productname qtyupdated
2011-01-29 12:25:47.253 p1 20
2011-02-22 10:25:47.253 p2 90
2011-02-22 11:25:47.253 p3 60
that is latest of each record.
Please help
select max(Updateddatetime) as Updateddatetime,Productname, max(qtyupdated) as qtyupdated from table
group by Productname
How would I select all from a previous month?
No,
This would not give the desired result.
max(Updateddatetime) will always give the latest date not all latest records.
Thanks for your reply.
Your query would only return the record with latest date but not all records.
In my case there are different products and the product quantities are not updated on daily basis.
For eg.
I have 3 products ..
Updateddatetime Productname qtyupdated
2011-01-28 10:25:47.253 p1 10
2011-01-29 12:25:47.253 p1 20
2011-02-20 10:25:47.253 p2 10
2011-02-22 10:25:47.253 p2 90
2011-02-20 10:25:47.253 p3 50
2011-02-22 11:25:47.253 p3 60
I should get the result as
Updateddatetime Productname qtyupdated
2011-01-29 12:25:47.253 p1 20
2011-02-22 10:25:47.253 p2 90
2011-02-22 11:25:47.253 p3 60
that is latest of each record.
Please help
i want date range of one month where any data entered between this month should not duplicate. if data entered after the month should allow to enter.
You need to check like this
if not exists(select * from table where date_col >=@date1 and date_col<dateadd(day,1,@date2))
insert statement
if i enter date 15-04-2015 then my output like this
januray
feburary
march
april
that will return all the previous month names of a particular year ,if date is passed to that function as an input parameter.
create a table with all months and their name. and use that along with input value to get rest of month.
Q1) Given Month, Year and Day.
List out all the “Monday” dates of current month and year
Q2) Suppose loan has been taken on 1-Jan-2001 and after exactly One Month the Due will come for Installment payment (i.e. on 1-Feb-2001)
So find out the Due date of the Installment if on any random date loan is given
IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END
–UNION ALL
IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END
hi all, i need to make union this two if condition Queries with groupby condition,
pls help…… Thanks in advance
Sir how can i show all months of a year ????
You need to have a calender table for this and query from that table
how i can show all months of a year
I need to find records dated last day of the month..any suggestions?
thx alot
Arthur, You are an [removed word]. Your method of calculating the last day of the month does not work. You should test your code before you post it for the world to see. I have wasted so much time attempting to implement your solution. Thanks
Steve: Arthur’s code does work, you just need to replace the single quotes with real single quotes. Unfortunately the html reply box on this page alters single quotes to this symbol ‘
Still, it’s not elegant as you have to supply both the year and month, and the datatype returned is a string so overall not very useful.
Thank’s a lot
hi ,
I m new to sql server.
I have question like i want to display records of last 30 days from currentDate with additional column say DayNo,monthNo,yearNo which contains only day,month,year…
Can anybody help me out…
Thanks .
Sir I want to calculate previous months records from my table .
like now is month january ,then i want to calculate dec 2011
count records .
Please help me
Hi Pinal,
I want a function to calculate the last day of all the months based on Year number and Month number but not based on GetDate() that gives the current Date.
Please help me out.
Thanks in Advance,
Samyuktha
Holy crap!
Can’t you just do this to get the last date of the month?
SELECT DATEADD(d, -1, DATEADD(M, DATEDIFF(M, 0, @SomeDate)+1, 0)) AS NewDate
Write a function to return total experience in ,months/years according to the given input(M/Y). Use this in a SP to get the details of a person with his name, experience.
how can i find out the last day of previous year using sql..
for example today is 03rd march 2012 , i have to get a result in the following format ‘2011-12-31’ Plz help me… thanks in advance
SELECT CONVERT(VARCHAR(10),DATEADD(MILLISECOND, -3,
DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1,
GETDATE())) + 1, 0)),120)
select dateadd(year,datediff(year,0,getdate()),0)-1
Best one who willing to learn SQL in Deep.