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
That was JUST what I wanted – you have saved a tired brain from thinking about a problem from the wrong direction
Thank you so much!!…You are a life saver !!
Wants to calculate weekly data from Monday to Sunday basis. But the doubt is, when i am calculating the data by year. weeks got divided into two parts, i.e current year and next year. i want to combine into one.
pls help some one.
Thanks PinalDave, you’re a ledge, you saved me doing any actual work!!
Hy, cool stuff! Perhaps you can help me with my problem. i need a query for getting the first day of any month as a a start day.
can you perhaps help me?
cheers stephan
Try this code
select dateadd(month,datediff(month,0,getdate()),0)
hy,
cool it works, but i would need a code which gives me the first day of every month in the next years …
thank you for your help!
stephan
This will do it
select dateadd(month,number,dateadd(year,datediff(year,-1,getdate()),0)) from master..spt_values
where type=’p’ and number between 0 and 11
Hy madhivanan!
great – thank you! it works really good.
How do I get the first day of the previous month without using the DateDiff function?
select date-day(date)+1 from
(
select getdate()-day(getdate()) as date
) as t
Can anyone please help me to print last seven days dates in sql. like below
today- 17/8/2011
16/8/2011
15/8/2011
14/8/2011
……
select dateadd(day,-number,getdate()) from master..spt_values
where type=’p’ and number between 0 and 6
select ‘FirstDay Of The Month’ as ‘Text’,convert(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106) ‘Date’
union all
select ‘LastDay Of The Month’,convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),106)
You can also use
select
dateadd(month,datediff(month,0,getdate()),0),
dateadd(month,datediff(month,0,getdate())+1,-1)
Thank you! Just what I needed.
hi,
how to find the last wednesday date from any given date?
Hi,
I need a Financial year end date for my report ie., Today date is ‘2012-01-07’ then Financial year end date should be ‘2012-03-31’ similarly if today date is ‘2012-04-01’ then Financial year end date should be ‘2013-03-31’. Please help me out….
declare @i int
set @i = 1
declare @date smalldatetime
drop table #days
create table #days(day_i smalldatetime)
set @date= ’02/01/2011′
insert into #days values(@date)
while (@i<31)
BEGIN
–select @date+1
insert into #days values(@date+1)
set @i = @i+1
set @date = @date +1
END
delete from #days where datepart(M,day_i) datepart(M,’02/01/2012′)
select * from #days
select min(day_i) as first_day,max(day_i) as last_day
from #days
The Examples provided above are really useful.
Thanks,
Rao Y. Kunche
Hi… I need a query to calculate running total of a particular column.But, it only display the final total in the last cell…
Hi… I need a query to calculate running total of a particular column.But, it only display the final total in the last cell…
It looks like:
amount Total
———- ——-
10
20
30
10 70
Please help me……
Hi….I need a query to get all the previous for the given month..for eg:If I enter 3 the query should display all Months before 3 i.e jan,feb,march……Please help me………
This way is very short and best to get DayOfMonth. if u like then plz tell me.
DECLARE @SystemDate DateTime, @StartDate DateTime, @EndDate DateTime
SET @SystemDate = ’26-Apr-2012′
SELECT @StartDate = DATEADD(dd, -Day(@SystemDate) + 1, @SystemDate)
SELECT @EndDate = CONVERT(VARCHAR(20), DATEADD(dd, -(DAY(DATEADD(mm, 1, @SystemDate))),DATEADD(mm, 1, @SystemDate)),101)
–SELECT @StartDate StartDate, @EndDate EndDate
SELECT DateDiff(WeekDay,@StartDate,@EndDate) + 1 AS DayOfMonth
from shah…
In Application take datetimepicker
& set format on load event
string fDate = string.Format(“{0:MMM-yyyy}”, dtpFromDt.Value);
it will show Dec-2010
Rajshri
I used the code above to create a function i use to find the 1st of the month. Use – argument for past and positive for future or 0 for first of present month.
CREATE FUNCTION dbo.Get1stMonthDateFromNow
(
@Months INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @CurrentMonth DATETIME
SET @CurrentMonth = CAST(
CONVERT(
VARCHAR(25),
DATEADD(dd, -(DAY(GETDATE()) -1), GETDATE()),
101
) AS DATETIME
)
RETURN DATEADD(m, @Months, @CurrentMonth)
END
go
SELECT dbo.Get1stMonthDateFromNow(0)
Find more such examples here
Thank you very much. Most useful