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.
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
Reference : Pinal Dave (http://blog.SQLAuthority.com) , This was requested by my co-worker.
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
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.
hi,good day…
thanks for ure codings above….but iam having problem to select the month and display the details…eg…..i have a table with the field which would have the date,month and year (fldMonth), if let say i select the year 2007 in another drop down list, i want the table to display the details starting from june 2007 till july 2008…..
i really have no idea…plz help me out…urgent…!!!currently my coding just display the details for that particular year onli from january to december…..
thanks lots
Get idea from this and apply it
declare @year int
set @year=2007
select dateadd(month,6-1,dateadd(year,@year-1900,0)),dateadd(month,6-1+13,dateadd(year,@year-1900,0))
Worked like a charm and saved a few brain ticks from figuring it out myself.
I used it for a financial report so I went a step further and did this for previous year dates. I also need to find previous year WTD so I used this:
DATEADD(wk, DATEDIFF(wk, 6, dateadd(yy,-1,@dToday)), 6)
How to get first and last working day of month? I mean exact DAY of week when we start working by excluding SATURDAY and SUNDAY if it comes on 1st or 2nd. So Query should return 3rd.
/*To find first day of previous/next month*/
declare @testdate datetime
select @testdate=getdate()
–Find first day of pevious month
SELECT DATEADD(dd,1-day((DATEADD(mm,-1,@testdate))),DATEADD(mm,-1,@testdate))
–Find first day of next month
SELECT DATEADD(dd,1-day((DATEADD(mm,1,@testdate))),DATEADD(mm,1,@testdate))
i have to store in the sql server database like 2008AUG how to do that
Why do you want to store formatted dates?
@nir
use master
create database “2008AUG”
– give your options
use double quotation around the name of the database and you can create a database name starting with a letter.
Thanks,
Imran.
hi I want to get the last day of the month between the two given dates. also I want to get the last day of the week between the two dates.
Hi I’m trying to retrieve data from a database which uses SQL, how do I specify which dates I require values for in the DD/MM format
Thanks for the article, gave me clue for my requirements, I have jotted down calculating various days like this day last year, first day of prior years and so on, hope this would be useful.
–This week start date Monday –
select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
–This week enddate–
–select dateadd(d,-1,getdate())
select cast(floor(cast(dateadd(d,-1,getdate()) as float)) as datetime)
–Last week start date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)
–Last week end date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
–First day of this year–
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
–Last day of the prior year–
select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)
–First day of next year–
select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)
–First day of prior year–
SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)
–This week last Year start date Monday –
select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)
–This day last year
select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
–First day of the current month–
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
–Last day of the current month–
select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)
–First day of the current month Last year–
select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
–Last day of the current month Last Year–
select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)
Hi,
Thnaks for your query, Is it possible that instead of sending date can I send only month. Could you please send me that query if possible.
Thanks in advance.
Regards
Suman
What about year part?
Do you want to send it too?
This article helped me in one query.But i’m trying another query,can you help me in ths?
I have a Sales table data.I have user submitting every month sales data.My company has financial year from OCt 1 st to Sept 30 the next year.Whenever, i run the query, i have to get totalSalesAmount,for Salesdate which ranges between Oct 1st to till date.If i run that query now i have to get sales amount(Oct 2008+Nov 2008+Dec 2008) but when i run it in March 2009, it should should fetch (Oct 2008+Noc 2008+Dec2008+Jan 2009+Feb2009),
How do i do that?
Help appreciated
Cool stuff.. great job keep rocking…
can anybody help me to find out second,third,fourth,fifth tuesday,wed,thu,fri,sat,sun of a month?
thanx
Hi,
For each month we have many weeks, some month will have 4 weeks but some will have 6 weeks ( for example for jan 09 , 01 -03 jan will be considered as Week1). How to find out the week number as per the above logic.
If i give a date the query should return it belongs wich week of the month.
i tried with datepart and week , but it returns week number for the year not for the month
Thanks
Hi i want the query to find the maximum date in a particular table.
Thanks,
Karthi..
select max(date_col) as max_date from your_table
Hi,
Can you help me to get a query in SQL server that gives me the date of last saturday of every month
Thanks,
Sanil
Hi,
I want to claculate age say for eg.
Tab1
——-
contribution_date Dob AgeAtContribution
15-12-2005 01-07-1977 ?
Want to calculate age at the time of contribution_date with the help of dob column above.
Thanks,
aak
Hi,
Here you can use computed column to get Age At contribution Date.
Example:
CREATE TABLE t1(
contribution_date DATETIME,
Dob DATETIME
)
INSERT INTO t1 (contribution_date, DOB) VALUES(NULL, ’1977-07-01′
)
ALTER TABLE t1
ADD AgeAtContribution AS DATEDIFF(yy, DOB, contribution_date)
SELECT * from t1
You can also find my blog to add computed column at:
http://tejasnshah.wordpress.com/2008/12/24/how-to-add-computed-column-sql-server-2005/
Thanks,
Tejas Shah
How do you get last day of month prior year for February where prior year was leap year?
Using the example above:
declare @period smalldatetime
set @period = ’2/28/2009′
select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,dateadd(mm,datediff(m,0,@period)+1,0)))as float)) as datetime)
Result: 2/28/2008
Need result to be 2/29/2008
This is what you need
select dateadd(month,2,dateadd(year,year(getdate())-1900-1,0))-1
@Verdel
Make the date March 1st, then subtract a day: select dateadd(yyyy, -1, dateadd(m, +1, dateadd(dd, – (datepart(dd, @period) – 1), @period)))
Suppose Getdate() output is 01/11/2009.
How to get previous month last day.
Out put should be 12/31/2008.
Let me know.
Thanks.
@nirav
Here you get last date of previous month
SELECT DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, GETDATE()), 0))
Hello,
I tried to create the SQL statement with the condition:
“Today’s date > Month 36 after first VISIT_date” ?
I would think about 36 months * 30 days as:
(SysDate – VISIT_date) > (36*30)
But not every month has 30 days. Can any one help pls?
Thanks,
-Vanessa
@Vanessa
How about: SysDate >= DATEADD(m, 36, VISIT_date)
Though, 36 months is 3 years, so: SysDate >= DATEADD(yy, 3, VISIT_date)
Found your queries helpful
hi,
am sreekanth, i have a table with some columns and date of update.i am writing function which takes month and year as parameters.i want max date of update from table where date of update <=(month and year) how to write a query.plz reply me to the mail if u know the solution
hey
i want to get all the month names using SQL Query plz help me out
thanks
tania
@Tania.
If you have SQL Server 2005 or higher , you can try this,
create table #temp(id int identity, ename varchar(10))
go
insert into #temp values (‘Testing’)
go 12 — This should not be replaced.
go
select datename (mm , dateadd (mm, id – (datepart(mm,getdate())), getdate())) Month_Names
from #temp
go
drop table #temp
go
I am sure, there is a better way of doing this, at-least this should give you a start.
~ IM.
I was going through this forum hoping to get the last day of year of a given date but can’t find how to get. Anyone can help me how to get it. I need this badly.
Thanks.
select dateadd(year,datediff(year,0,@date)+1,-1)
Haamidou,
Try this,
Select Datename ( dw, Dateadd ( dd , -1 , datename (yy, dateadd (year, +1, getdate()))))
Output : Thursday
Replace Getdate() with your date column name.
Please test this before using it in your code.
~ IM.
Thanks a lot Imran. I also got how to get the end date as well i.e. December 31st of any given date.
Hellooo…
M the student of MCA n i hv such a query….
der detail is as follow….
-> PRINT THE INFORMATION OF CUSTOMERS WHO HAVE BEEN BORN IN THE MONTH OF FEBRUARY.
@Devika
Take a look at the DATEPART FUNCTION:
WHERE DATEPART(m, date-col) = 2
Shortly
WHERE month(date_col) = 2
I need to know the concept of Parameterized views?? Is it realted to functions returning table are parameterized views??
How these are different then Stored procedures.??
Please tell me!!
Hi,
I want query that will display the all the sundays with date that are coming in the whole year, or the year that i will pass as a parameter.
Thanks in advance,
regards,
yogesh
Hi,
Yogesh you can find out all sundays for given year by this query.
What I did is:
First I get First Date of given year and Last date of given year. Then I used CTE (common Table expression), to get all dates.
DECLARE @Year AS INT, @FirstDateOfYear DATETIME, @LastDateOfYear DATETIME
SELECT @year = 2009
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year – 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year – 1900 + 1, 0)
;with cte as(
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date FROM CTE
WHERE DayName LIKE 'SunDay'
option (MaxRecursion 370)
Let me know if you have any doubts.
Thanks,
Tejas
SQLYoga.com
very good Tejas
Or you can use the below query :
create table #temp (i int identity(1,1),c1 char(1))
insert into #temp (c1) values(‘a’)
go 1000
select dateadd(“dd”,i,GETDATE()) dtDate from #temp where datename(dw,dateadd(“dd”,i,GETDATE())) = ‘Sunday’
Regards,
Pinal Dave
[...] question: how to generate the date for all the Sundays in the upcoming year. Tejas replied here with a [...]
hi,
i want to know
how to write a query that gets no of days in month and year.
to get days in a month(yearwise)
Dear Pinal Dave,
i am new in database site but i got good knowledge from you blog nice efforts helping db developer by ur skill well dear i want help concerning date issue, will you solve “how to get all dates of current month.”
thanks
;WITH CTE AS
(
SELECT DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS FIRSTDATE
UNION ALL
SELECT DATEADD(dd,1,firstdate) from CTE where FIRSTDATE<=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,getdate())),101)
)
select CONVERT(varchar(25),FIRSTDATE,101) from CTE
Thanks for the query.
hey..
I’m looking to get the day from a date.
How do i get it?
suppose I give some date format in the sql query, lets say for example “10-Apr-98″, I would like to get the result as which day this date has occurred? Like.. saturday or sunday .. etc??
Can someone help me how to do it?
Thx !!
select datename(weekday,’10-Apr-98′)
hello,
I’m looking to get the for every day of the month only a special time..
for example 01.04.2010-30.04.2010; every day 8-12 clock
Can someone help me how to do it?
thanks
where datepart(hour,date_col)>=8 and datepart(hour,date_col)<13
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)
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
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
SQL SERVER – Query to Find First and Last Day of Financial Year
Query to Find First and Last Day of Financial Year.
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
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
Thank you very much. Most useful
Lazy solution: for fast day of the month
select ’1/’+
CONVERT(varchar(25),(select MONTH(getdate())))
+’/'+
CONVERT(varchar(25),(select YEAR(getdate()) ))
*First
Thats a VARCHAR and not DATE. Use this approach
select dateadd(day,datediff(day,0,getdate()),0)
Dear Mohamed, this is shah. I provide the solution of getting Days in the month. and u provide something else
BUDDHI Srilanka
Some one need to get previous year Current month start and end date
DECLARE @mydate DATETIME
DECLARE @FdayOfCurrentMonth DATETIME
DECLARE @LdayOfCurrentMonth DATETIME
DECLARE @FdayOfCurrentMonthLY DATETIME
DECLARE @LdayOfCurrentMonthLY DATETIME
BEGIN
SELECT @mydate = GETDATE()
SELECT @FdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
SELECT @LdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
SELECT @FdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-12,@mydate)),101)
SELECT @LdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,-11,@mydate)),101)
Dont convert them to VARCHAR. Use the examples shown here. Go to the bottom of the page and refer the examples http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
Thanks a lot, helped me quite a bit. :)
Hi I am in the way of automating my SQL query. I have a filed called Transaction_Date in my table.
I my analysis i will pre period analysis, promo period analysis and post period analysis. FOr ex, if the campaign starts on 1-June-2012 and the end date is on 30-Jun-12, then i should take the same number days as promo period for pre and post anlaysis.
I am giving the dates manually in hte SQL code. But i want to automate it by considering the dates automatically.
drop table #response_curve
select Status,
week_no,
[Customers] = count (distinct account_no),
[Transactions] = sum(case when tx_type_code in (’01′,’90′) then 1 when tx_type_code in (’03′,’94′) then -1 else 0 end),
[Spend] = sum(cast(total_txn_value*issue_mult as bigint)),
[Standard_Points_Issued]= sum(cast(std_points_value*issue_mult as bigint)),
[Bonus_Points_Issued] = sum(cast(bonus_points_value*issue_mult as bigint))
into #response_curve
from
(select case when t.transaction_date >= ’2012-07-17′ and t.transaction_date = ’2012-07-22′ and t.transaction_date = ’2012-07-29′ and t.transaction_date = ’2012-08-05′ and t.transaction_date = ’2012-08-12′ and t.transaction_date = ’2012-08-19′ and t.transaction_date = ’2012-08-26′ then ‘Week7′end as week_no,
t.account_no,tt.tx_type_code ,t.total_txn_value,tt.issue_mult,t.std_points_value,t.bonus_points_value,t.transaction_date,f.Status
from tx_txn t with
inner join #final_campaign_members f on f.account_no = t.account_no
inner join tx_type tt on t.tx_type_code = tt.tx_type_code
where t.terminal_id like ‘RSA%’ and t.transaction_date >= ’2012-07-17′ and t.transaction_date < '2012-09-01' ) a
group by Status,week_no
Above is the code which i am using. please help me on howto automate the above code by not giving the dates manually
Hi I need a help with the following criteria in SQL query.
I have an end date of say ex. 4/15/2012 and I want to set this date to end of the month ie, 4/30/2012.
Similarly, I have a beginning date as 4/16/2012 and I want to set it first of next month ie 5/1/2012. Can someone help me on this plz.
Hi all,
I need a querry that can give me endate from start date and number of months
Like I have a start date is 10/1/2012 and number of months is 10, how can i get 7/31/2013
Here’s the correct version for the blogger’s queries that takes into account the times 00:00:00 and 23:59:00
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0), ‘Start Last Month’
SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)), ‘End Start Last Month’
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), ‘Start This Month’
SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)), ‘End This Month’
How to get the month having maximum birth day in a particular year in table having field DOB with datetime in sql server?
Saved my time :):)
i need to fetch all the details fom database by filtering name and from(startdate) to (enddate) if a name is kannan i need all his details such as projectname,task details within that filtered date for timesheet eg he done various task and list all the task he made
Do not use string concatenation. Use DATADD/DATEDIFF functions. Run the below query
SELECT
DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) ThisQuarterFirstDay,
DATEADD(QQ, 0, GETDATE()) ThisQuarterLastDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) – 1, 0) LastMonthFirstDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), -1) LastMonthLastDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) ThisMonthFirstDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, -1) ThisMonthLastDay