Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.
DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101), 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))), DATEADD(mm,1,@mydate)),101), 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1), DATEADD(mm,1,@mydate)),101), 'First Day of Next Month' GO
Here are few additional blog posts on this subject:
- Adding Datetime and Time Values Using Variables
- Find Current System Date, Time and Time Offset
- Get Time in Hour, Minute Format from a Datetime – Get Date Part Only from Datetime
I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.
Reference: Pinal Dave (https://blog.sqlauthority.com)
188 Comments. Leave new
Your SQL queries for working out dates helped so much. I was on the right track but not quite there
— Last days of previous months
I have a small query I recently used for quarterly report.
you can mondify to trunc by month ‘M’.
select
sysdate,
trunc(to_date(Add_months(sysdate,-3)), ‘Q’) “From Date”,
trunc(sysdate,’Q’)- 1 “to Date”
from dual
where EXTRACT(MONTH FROM sysdate) in(1,4,7,10);
Thanks for the information
hi,
I have two tables reciept and issue.
Reciept have the foloowing details
partno date rquantity rrate
a 10/1/2005 50 15
a 15/4/2005 10 22
a 11/7/2005 40 20
b 10/6/2005 30 20
b 25/6/2005 15 22
Issue have the foloowing details
partno date iquantity irate sval lval
a 14/1/2005 10 10
a 15/5/2005 20 22
a 1/8/2005 20 22
b 11/7/2005 10 20
what i have to do is
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calcucated result in “lVal” and ‘sval’ column
iquantity(irate)+iquantity(irate-rrate)
example, first we have to subtract
10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50
20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440
20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440.
and also if the date difference between dates of two tables are greater than 365 days then they have to stored in the column lval otherwise in the column sval. And this should be repeated to all items.
Good article, thanks
Hi
i am looking for the function which gives last working day of the month for the given time period
example:it should be like this
getlastdayofmonth(@start_date,@end_date)
–so it should give all the dates (last day of month) between start_date and end_date
really apreciate if anyone can help with this
thank you
I have used the code you wrote as follows to find the start of the current and previous months.
{
declare @mydate datetime
select @mydate = getdate()
SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120) as ‘StartDate’
SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120) as ‘EndDate’
}
However to use these I need them to be in date format with the times represented as 00:00:00:000 irrespective of what time of day the select statements are run.
Is this possible ?
I ran the following code as part of a statement to capture
something occuring between the first of the current month and the end of the current month however when ever i refrence Stardate or Enddate i get the following error. Any ideas how to make the columns remain in effect throuhout the entire query?
Msg 207, Level 16, State 3, Line 10
Invalid column name ‘Startdate’.
Msg 207, Level 16, State 3, Line 10
Invalid column name ‘Enddate’.
declare @mydate datetime
select @mydate = getdate()
SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120) as ‘StartDate’
SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120) as ‘EndDate’
This is a great reference! Thank you so much!
I would like to ask how I could return the date value with the time as well.
For example,
I want the result to be
’12/01/2009 00:00:00′
What needs to be done to the above code to achieve this?
Thanks!
Joe
select dateadd(day,datediff(day,0,date_col),0) from table
SELECT CAST( CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS DATETIME)
Hi all
The folowing piece of code gets the first day of the next month. so if you have 20 january the function finds 1 february as result.
DECLARE @datum DATETIME
SET @datum=’2007-01-31′
DECLARE @datFuture DATETIME
SET @datFuture = dateadd(mm,1,@datum)
DECLARE @dateFirstDayNewMonth datetime
SET @dateFirstDayNewMonth = cast(year(@datFuture) as varchar(50)) + ‘-‘ + cast(month(@datFuture) as varchar(50)) + ‘-1’
print @dateFirstDayNewMonth
It could be easily done with
DECLARE @datum DATETIME
SET @datum=’2007-01-31′
select dateadd(month,datediff(month,0,@datum)+1,0)
Hi. How do I get/print all the months between 2 dates, eg. 01 Aug 2006 and 31 March 2007? Eg.
Aug 2006
Sep 2006
Oct 2006
Nov 2006
Dec 2006
Jan 2007
Feb 2007
Mar 2007
declare @date1 datetime,@date2 datetime
select @date1=’01 Aug 2006′, @date2 =’31 mar 2007′
select dateadd(month,number,@date1) from master..spt_values
where type=’p’ and number between 0 and datediff(month,@date1,@date2)
DECLARE @date1 DATETIME,
@date2 DATETIME
SELECT @date1 = ’01 Aug 2006′,
@date2 = ’31 mar 2007′;
WITH months(monthnames)
AS (SELECT @date1 AS monthnames
UNION ALL
SELECT DATEADD(mm,1,monthnames)
FROM months
WHERE monthnames < @date2)
SELECT STUFF(CONVERT(VARCHAR(12),monthnames,106),1,3,'') AS monthname
FROM months
OPTION (MAXRECURSION 0)
what about first and last working day of month?
Hi
I have to create a query where the month we are selecting must match with the current month.
Like if i select January and in database if there is 2 set of data , January and December, it has to select january data and display.
where
date_col>=dateadd(month,datediff(month,0,getdate()),0) and
date_col<dateadd(month,datediff(month,0,getdate())+1,0)
hi…
I am having problem on finding a first day of a month.. plz help me..
send me a set of codings..
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
‘First Day of Current Month’ AS Date_Type
or
select dateadd(month,datediff(month,0,@date),0)
How do you find the first full week of the month?
Hi
I am looking for a query, which get me week’s first day and same week’s last day…
please help me out
Thanks and Regards
hi…
I am having problem on finding a first day of a month.. plz help me..
send me a set of codings..
Use this code
select dateadd(month,datediff(month,0,getdate()),0)
Hi,
How to get the last date of sunday of everymonth..plz help me….
Hey Pinal,
Your queries helped a lot
Thanks :)
Cheers
Suruchi
hi…
it s very useful to me…………thanx…….
i have a problem that…..how can i display the group of date which should fall under 1st april n 31st of april ie one full month …like wise all the months…….??
can u help me pls???
use this simple query to get first date of last month
select cast(cast(datepart(month, dateadd(mm, -1, getdate())) as varchar) +
‘/’ +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))
as varchar) + ‘/’ +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)
Use this to get previous months last date
SELECT convert(varchar,DATEADD (day, – datepart(dd,getdate()), getdate()),101)
Thank you for the query. :)
Thanks for the query! It saved me tons of time.