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
hi
i want to find out a first day of month
select dateadd(month,datediff(month,0,getdate()),0)
hi
I want to get last months first date.
today is 1/06/2010, i want to get as 01/05/2010
select dateadd(month,datediff(month,0,’20100501′)-1,0)
SELECT dateadd(m,datediff(m,0,GETDATE())-1,0)
I need a SQL query for tickets that are currently open. And tickets closed since the first day of the current month.
Post table structure, sample data with expected result
Hi,
I want to get the fiscal period of any year for example
Input : June 2010
Results desired
Period…………….Start…………………………………..End
1…………………1-06-2010……………………………1-07-2010
And so on untill May 2010 …what will be the query.
Thanks in Advance
Hi Josh,
If you are using SQL 2005 and above edition, you can use Recursive CTE to generate this type of result.
you can write following query:
DECLARE @InputDate SMALLDATETIME, @OutputDate SMALLDATETIME
SELECT @InputDate = ‘2010-06-01′
SELECT @OutputDate = DATEADD(mm,12,’2010-06-01’)
;with cte as(
SELECT @InputDate As StartDate,
DATEADD(mm,1,@InputDate) AS EndDate
UNION ALL
SELECT DATEADD(mm,1,c.StartDate) As StartDate,
DATEADD(mm,1,c.EndDate) AS EndDate
FROM cte c
WHERE DATEADD(mm,1,c.StartDate) < @OutputDate
)
SELECT *
FROM Cte
Let me know if it helps you.
Thanks,
Tejas
SQLYoga.com
Thanks Tejas,
I’ve solved it using below i think I didnt describe my output properly.
DECLARE @StartDate DATETIME = ’06/1/2010′;
WITH periods AS
(SELECT 1 AS period
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
)
SELECT period,
DATEADD(month,period-1,@StartDate) as startDate,
DATEADD(day,-1,DATEADD(month,period,@StartDate)) as endDate
FROM periods
Hi,
I need to calculate age of a member turning 18 this year starting from current month to this year.
I’m working on this formula
(DATEDIFF(year, bdate, DATEADD(s, – 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) = 18)
this gives me members turning 18 starting from January. Can anyone help me out in this.
Thanks
Zara
Also, if i want to get age for certain months, for example members turning 18 from August to November or from January to March.
I appreciate your help
You made my day. Very much appreciate your help.
Thanks,
Rahul
Hi. How do I get/print all the months between 2 dates,
for Ex. 01 Apr 2010 and 30 Apr 2010 ? Eg.
Please Tell Me .
Apr 2010
declare @date varchar(20)
set @date=’Apr 2000′
select dateadd(day,number,cast(@date as datetime)) from master..spt_values where type=’p’ and number between 0 and 29
I need to two reports getting the 1st thru the 15th and the 16th to the end of the month. The first report runs on the 16th of the month and the 2nd report runs on the 1st or the next month.
What is the code that you are currently using? Did you find difficulties?
Thanks Pinal, thanks for such helpful code.
HI
I want to get start date and end date of financial year.
My input will be year and according to it I should get start date and end date of fiscal year
Thanks
DECLARE @TodayDate DATETIME,
@PreviousMonth DATETIME,
@PreviousMonthLast DATETIME
select @TodayDate = ’02-Mar-2010′
print @TodayDate
–Previous Month
SET @PreviousMonth =DateAdd (MONTH, -1, @TodayDate)
print @PreviousMonth
–1st day of PreviousMonth
SET @PreviousMonth = DateAdd(D, ( -DAY(@PreviousMonth) ) + 1, @PreviousMonth)
print @PreviousMonth
–last day or previous month
SET @PreviousMonthLast = DateAdd(DAY, -1, DateAdd(MONTH, 1, @PreviousMonth))
print @PreviousMonthLast
HI,
I am working on a report and I have a where condition in the query where( Time between (@Startdayofweek) and (@Enddayofweek)). If I select a Month then it show all the values for @EndDayofWeek(i.e.,fridays) and @startdayofweek (i.e., Mondays) should select values based on the @Enddayofweek. I am able to get all the values for @Enddayofweek(i.,fridays) but @startdayofweek parameter is taking only one value from the @Enddayofweek. here is my query for @enddayofweek and @Startdayofweek
@Enddayofweek=select CONVERT(VARCHAR(12), DATEADD(DD, 6 – DATEPART(dw,day), day), 10) as enday
where month=’feb’ and year=’2010′
(I have a table called time and in that table it has days,year and month)
@Startdayofweek= SELECT CONVERT(Varchar(10), DATEADD(day, – 6, @enddayofweek), 101) AS startday
Please help me with this
Thanks,
Kumar
Can you post some sample data with expected result?
Excellent post, this code has helped me immensely.
Hi there,
Very helpful content. Would you please help me to know how to get the last day of next month ? As a suggestion, it would be nice if you give a short description of what your statement is doing or how it is calculating.
Thanks
select dateadd(month,datediff(month,0,getdate())+2,-1)
The datediff function calculates the difference between based date 1900-01-01 to current date. 2 is added to get the date of two months greater than today. The month difference is added to based date again and to create a first day and subtracting 1 will give you last data of next month
HI……….
I AM WORKING ON A REPORT
I HAVE TABLE LIKE
IMG_NO FLAG LOG USR_ID
W01 1 4/5/2011 1
W01 4 4/5/2011 1
W02 1 4/5/2011 1
W03 1 4/5/2011 1
W03 2 4/5/2011 1
W04 5 4/5/2011 2
W05 1 4/5/2011 2
W05 4 4/5/2011 2
W05 5 4/5/2011 2
W06 1 4/5/2011 2
W06 1 4/5/2011 2
SO I WONT OUTPUT LIKE
USR_ID STARTIMGNO ENDIMGNO FLAGIS4 FLAGIS’5 TOT
1 W01 W03 1 0 5
2 W04 W06 1 2 6
THE ABOVE OUTPUT BASED ON DATE PLZZZZZZZZZZZZZZZ
HELP ME
THANKS.
HI……….
I AM WORKING ON A REPORT
I HAVE TABLE LIKE
IMG_NO FLAG LOG USR_ID
W01 1 4/5/2011 1
W01 4 4/5/2011 1
W02 1 4/5/2011 1
W03 1 4/5/2011 1
W03 2 4/5/2011 1
W04 5 4/5/2011 2
W05 1 4/5/2011 2
W05 4 4/5/2011 2
W05 5 4/5/2011 2
W06 1 4/5/2011 2
W06 1 4/5/2011 2
SO I WONT OUTPUT LIKE
USR_ID STARTIMGNO ENDIMGNO FLAGIS4 FLAGIS’5 TOT
1 W01 W03 1 0 5
2 W04 W06 1 2 6
THE ABOVE OUTPUT BASED ON DATE PLZZZZZZZZZZZZZZZ
HELP ME
THANKS.
Hi All,
Can someone please help me in finding the first and last date of the current month.
Regards,
Veena
select
dateadd(month,datediff(month,0,getdate()),0) as first_day,
dateadd(month,datediff(month,-1,getdate()),-1) as last_day
You may alos need to read this for more informations
SQL SERVER – Query to Find First and Last Day of Financial Year
Query to Find First and Last Day of Financial Year.