Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions.
----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day of Current Week
————————
2008-08-31 00:00:00.000
First Day of Last Week
———————–
2008-08-18 00:00:00.000
Last Day of Last Week
———————–
2008-08-24 00:00:00.000
First Day of Current Month
————————–
2008-08-01 00:00:00.000
Last Day of Current Month
————————-
2008-08-31 23:59:59.997
First Day of Last Month
———————–
2008-07-01 00:00:00.000
Last Day of Last Month
———————–
2008-07-31 23:59:59.997
First Day of Current Year
————————-
2008-01-01 00:00:00.000
Last Day of Current Year
————————
2008-12-31 23:59:59.997
First Day of Last Year
———————–
2007-01-01 00:00:00.000
Last Day of Last Year
———————–
2007-12-31 23:59:59.997
Reference : Pinal Dave (http://blog.SQLAuthority.com), Vivek Jamwal
thx! @ll in 1 place :) Great! fav+
–First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)
–Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0))
–First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0)
–Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0))
Hi,
The above informations are really very fantastic..
May i know the how to down load sqlserver 2008 book.
Great info indeed (and your blog in general is full of ressources).
For some reporting, I need the first and last days of previous month (those I got from your useful dateTime functions) but also the first and last days of 2 months ago as well as the first and last days of 3 months… what ever is the day of today (getdate()). Any hint?
Make sure to read this post to know how to query on dates
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
This collection will be useful for everybody, wonderful
Hey.., information was sufficient enough…, thanks and good bye
When it comes to processing dates, one thing I failed to see online is the calculation for Retail Calendar. I’m in the Retail industry and calendar is different from the Gregorian calendar. Any clue in developing a Retail Calendar other then creating a custom calendar table?
Thanks,
Chris
what if you want results from a specific date? for example, the 3rd of every month?
select dateadd(month,datediff(month,0,getdate()),0)+2
Great Information .Thank You
it s most usefull for developer
Thanks so much! A real life saver….well, at the very least, a huge time and mental anguish saver.
Very useful functions no doubt , real life saver Thanks.
Very useful indeed. Thanks.
Hi Dave,
Thank you for all you have given, all your efforts, all your sharing and goodness to so many.
I pray great blessings will come your way for all those you have helped.
RSJ
i want to know the query of the month difference between two date as specific month name in sql server 2005! r u help me .
Hi Rajnish,
You can use:
DATEDIFF(mm, Date1, Date2) to get month difference between Dates.
Thanks,
Tejas
I\’d like to know how to get three months before today so that it changes every day that I open up MS SQL Server 2005. Does anyone know?
select dateadd(month,-3,getdate())
It’s ok I’ve found out now for myself. Gotta love S Q L!
Hi Dave,
Thanq very much for the above information.I appreciate your efforts
Great Tips,
Great Article and BEST SITE!!! You are hero!
Hi, I always read your posts, but this one was the best.
Thanks
Hi,
Its really very helpful information.
Thanks a lot.
It saves lot of development&/RD work.
Thanks Again !!!
Shahaji Udar
Pune(India)
Actually, GETDATE alone (the first item you have listed) is not equivalent to ‘Today’ but to ‘Now’ because it includes the time.
This would be a problem in cases such as if you want a where clause that only includes records before today.
In SQL Server 2008, you can take advantage of the Date data type to get a ‘Today’ function with today’s current date, but no time value (equal to a time of 00:00:00) like this:
CAST(GETDATE() as DATE)
For SQL Server 2005 and below, you have to go to more trouble to extract the year, month, and day and compare them all.
Note that in SQL Server Reporting Services, there is a TODAY function, but naturally, it only works in SSRS.
Sql Server 2005
Today = select convert(nvarchar(12), getdate(), 101)
Sudhir
You can just create a function called DateOnly()
create function [dbo].[DateOnly](@DateTime DateTime)
– Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
GO
Hi,
Thanks for posting the useful script.
But, the script can be easily understood if we replace 0 by ’01/01/1900′. Still, its very useful piece of code.
Thanks,
Sanjeev Puri.
Yes you can use but make sure to use unambigious format YYYYMMDD. Read this for more information http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-ii.aspx
I want to calculate DayOfQuarter for the given date. Any idea, how we can do it?
Thanks,
Sanjeev Puri
–First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)
–Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0))
–First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0)
–Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0))
Awesome – exactly what I was looking for! Thanks
Here is the answer for the question for the person who wanted to know what the day in the quarter was. I didn’t see this until this morning…
Have a good day…
jeff
——–
/* — function part commented out so you can test.
CREATE FUNCTION [dbo].[NumberOfDaysInCurrentQuarter] ( @dtInput DATETIME )
RETURNS INT
BEGIN
*/
DECLARE @dtStartOfQuarter DATETIME
DECLARE @nDaysInQuarter int;
— you don’t need the following three lines if you make this a function
— these are just so you can run in the management studio to test.
declare @dtInput datetime;
set @dtInput = GETDATE();
SET @dtStartOfQuarter = CAST(YEAR(@dtInput) AS VARCHAR(4)) + ‘-’ +
CAST(DATEPART(Q, @dtInput) * 3 – 2 AS VARCHAR(2)) + ‘-01′
print ‘@dtStartOfQuarter = ‘ + convert(varchar, @dtStartOfQuarter, 121);
set @nDaysInQuarter = DATEDIFF(day, @dtStartOfQuarter, @dtInput);
print ‘@nDaysInQuarter = ‘ + cast(@nDaysInQuarter as varchar(4));
/*
RETURN @nDaysInQuarter ;
END
GO
*/
HI,
Can you help me to write a code for calculating previous month value and previous 6 months values? I have monthyear object and month object in universe and db is sql server 2008.
Thanks,
Raj.
Post table structure, sample data and expected result
Hi Madhivanan,
Thanks for your reply.
I have a monthyear object on which i wnat to apply a condition to get previous month data and also previous 6 months data dynamically. The date format is like 1/1/2009. Data in the db need not be upto date so i cant use curent date or sysdate. I have to write a condition using this to create those filters. I also have another object callled monthyear-char. it has values like apr 2010. I can use either of objects to create those filters.
Suppose i have data until aug 2010 then if i use previous month condition, then july 2010 data should display.
I appreciate if you can help me in this regard.
Db is sql server 2008.
Thanks,
Raj.
where datecol>=dateadd(month,-1,@monthyear) and datecol<dateadd(month,0,@monthyear)
Hi Madhivanan,
Thanks for your reply, hope it solves my issue.
Thanks,
Raj.
Thanks a lot for this article. Was very helpful in writing some specific dates.
Can you please help with a SQL query for returning the year, then month, date from a table that has datetime values like this: 12/10/2010 and 11/7/2008. My customer wants to search the database using a specific date and that portion works, however he also wants to search by all the recoreds in the database and return all the dates of surgery in chronological order – latest dates first. So it would return 12/31/2010 first and 11/7/2008 last.
I used and surgDate>=dateadd(month,-1,@monthyear) and surgDate<dateadd(month,0,@monthyear and indeed it returns all the dates in chrono order starting with the most recent year…so this part works fine. However, when I allow the user to provide a specific date, the query returns nothing. I am using AND surgDate LIKE '%#(surgDate)#%' to determine if there are any dates "LIKE" the one the user has provided in the form…but I understand that this does not work the same as strings…any suggestion would be appreciated. Thank you.
You can use a number table and increment the date value till it ends with the last date
Hi , I want to get start and end date for each month in a given year what can i do for this? Help me to complete this task…
start with this
select dateadd(month,datediff(month,0,getdate()),0) as first_day,dateadd(month,datediff(month,-1,getdate()),-1) as last_day
Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
Thanks for ur reply … This query for only one month but i want the results for 12 months for a given year.. what can i do for this…
Thanks for ur reply … This query for only one month but i want the results for 12 months for a given year.. what can i do for this…
select
dateadd(month,number,dateadd(month,datediff(month,0,getdate()),0)) as first_day,
dateadd(month,number,dateadd(month,datediff(month,-1,getdate()),-1)) as last_day
from master..spt_values
where type=’p’ and number between 0 and 11
Really helpful date formats….all in one
Thanks
Friends, as we start our week on a “Sat” and end on a “Fri”
SET DATEFIRST 6
SELECT GETDATE() CurrentDate,
DATEADD(dd,(DATEPART(dw, GETDATE()) – 1) * – 1, GETDATE()) firstDayOfWeek,
DATEADD(dd, (7 – DATEPART(dw,GETDATE())), GETDATE()) lastDayOfWeek
Regards
Hi,
To get the EventDate for last month I used
SELECT * From SomeTable
Where EventDate Between DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AND DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))
Now users want only to see the EventDate between hours from 4pm to 6am of every day of last month. Can i please have some hints?
Thanks in advance!
The examples posted in this blog post will show you how to use that way in the where clause
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx
Hi folks, wow, just came across this great site.
I have a bunch of dates where i need to find the Appropriate Quarter for each date and Appropriate Year so i can create a Pivot Grid.
Any help appreciated.
Brian
Please refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx
The following will also help you
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx
Product Sales Report
ID Pro_Name Pro_Cost No. Of Units Bill Pro_Cat_ ID Date
1 Nokia C7 18000.00 1 18000.00 5 5/22/2010
2 Iron Box 2000.00 1 2000.00 4 1/31/2010
3 Dell Laptop 30000.00 1 30000.00 3 7/18/2010
4 Rice 600.00 4 2400.00 1 9/28/2010
5 HP Printer 5000.00 1 5000.00 3 11/26/2010
6 Horlicks 150.00 1 150.00 1 2/15/2010
7 HP Laptop 30000.00 1 30000.00 3 7/18/2010
Product Category Table
Pro_Cat_ ID Pro_Cat_Name
1 Home
2 Business
3 Computers
4 Electronics
5 Mobiles
Expected output
Quarter Home Business Computers Electronics Mobiles
Q1 - - - 2,000.00 -
Q2 150 - - - 18,000.00
Q3 2,400.00 - 60,000.00 - -
Q4 - - 5,000.00 - -
How can i do that?
Hi Sure,
(Brian, I think this might answer you pivot question as well).
You question seems to be way off topic. What you are looking for is not a date function but take a look at this articles about pivot tables, dynamic columns, and one or two other methods to implement what you are looking for. Basically you are trying to do group by
cast(year(dtSale) as varchar(5))+’-Q’
+ cast(datepart(qq, dtSale) as varchar)
which give you the format YYYY-Qn
My solution uses computed columns, and give you a running total for the quarter. The quarter is a bit messy to give you YYYY-Qn format but it works.
Standard disclaimer applies this hasn’t been tested against a large sample size for query performance, blah, blah.
Here is a the code to what you want. Again I would do research on what you trying to do with pivot tables.
FYI,
jeff
——
– drop table #prodTest;
create table #prodTest
(
id int identity(1,1),
stProduct_name varchar(150) NOT NULL,
stProduct_catagory varchar(80) NOT NULL,
mnPriceUnit money,
nQuantity int,
— computed column
mnPriceSale as (nQuantity * mnPriceUnit),
dtSale datetime NOT NULL default getdate(),
— use money cause in your example it would get to big too quick with decimal
);
/*
– using sql one insert to multiple rows.
– if you are using sql 2005 then do the select union all to
– make it work
*/
– drop table #prodTest
insert into #prodTest
(
stProduct_name,
stProduct_catagory,
mnPriceUnit,
nQuantity,
dtSale
)
VALUES
(‘Nokia C7′, 5, 18000.00, 1, ’5/22/2010′),
(‘Iron Box’, 4, 2000.00, 1, ’1/31/2010′),
(‘Dell Laptop’, 3, 30000.00, 1, ’7/18/2010′),
(‘Rice’, 1, 600.00, 4, ’9/28/2010′),
(’5 HP Printer’,3, 5000.00, 3, ’11/26/2010′),
(‘Horlicks’, 1, 150.00, 1, ’2/15/2010′),
(‘HP Laptop’, 3, 30000.00, 1, ’7/18/2010′);
SELECT * FROM #prodTest order by dtSale desc;
select
— this gets your YYYY-Qn Format
–
cast(year(dtSale) as varchar(5))+’-Q’
+ cast(datepart(qq, dtSale) as varchar) as [Year_Quarter],
— now create a table you want
sum(case when stProduct_catagory=’1′ then mnPriceSale else 0 end) as [Home],
sum(case when stProduct_catagory=’2′ then mnPriceSale else 0 end) as [Business],
sum(case when stProduct_catagory=’3′ then mnPriceSale else 0 end) as [Computers],
sum(case when stProduct_catagory=’4′ then mnPriceSale else 0 end) as [Electronics],
sum(case when stProduct_catagory=’5′ then mnPriceSale else 0 end) as [Mobiles],
sum(mnPriceSale) as [Quarter_Total]
from
#prodTest
group by
cast(year(dtSale) as varchar(5))+’-Q’
+ cast(datepart(qq, dtSale) as varchar)
order by
Year_Quarter desc;
—-Today
SELECT GetUTCDate() ‘UTC Time’
SELECT DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) will give me at midnight last night.
If I would like to have the time set-up to be 11:30 p.m., (23 hours ahead), what SQL statement should I use?
Thanks.
This is just perfect my friend. Really helpfull. Thanks, George from Buenos Aires.
great article, very helpful thank you.
how to query–
businessDate = 1 Business day prior to the 10th of each month
if date is Tuesday 1/10 then businessdate=1/9 which is monday
but if date is sunday 1/10,sunday is not business day so prior to business day,bsiness date=thursday.(prior to busness day friday)
column–weekday as sun,mon,tue…
dayofmonth as 1,2,3,4,…31
businessday as 0 and 1.if 0 holiday,1 as business day
awesome. helped to recapitulate. understand the logic and it is as easy as abc.
I liked the ideas in this post. Just another question. My client start the new month on the 26th day. e.g. 26 December 2011 = 1 January 2012 = first day of month January 2012.
Do anyone know a quick way to calculate these dates.
What I intend doing is to have a date lookup table where the ID is 20111226, the date is 26/12/2011 and the financial date is 1 January 2012.
I need to populate the table from 1 Jan 2000 to 31 Dec 2050
Any ideas?
Start with this
select dateadd(day,number,’20001010′) as dates from master..spt_values
where type=’p’ order by number
Hi, Any body can tell me that how to find last sunday date of the current month and next month.
Thank you very much for this post, it saved me a lot of time!
Just wondering how to incorporate a month end split. I want to use the weeks as listed above, but if the ‘Wednesday’ is the end of the month, I want to see that day instead of the last day of the week (instead of Saturday). My groups are already set, now I am just working on the labels, which is where my problem is occuring.
thanks in advance
Hi Pinal..
Is there any way to retrieve records using the IN clause for datetime datatype.
for eg: Select * from table where date IN (’2012-03-04′,’2012-03-05′,..)
RJP
Yes it is possible if the date does not have time part
I’m trying to run these in SS 2008. I’m not getting accurate results. It’s 1 day off. I copied them exactly & it’s showing the 1st day of the week as 4/2 instead of 4/1 & the same for the last day as it’s showing off by 1 as well.
I need to get totals for the full prior week Sun-Sat for a report that can be run anytime. I’m trying to figure out how to code for that. Help?
how to get record from today to 1 month ago date eg(19/5/2012 to pas 20/04/2012)
Again here is the link
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
how to get record from todays to 5 day ago of working day eg(today is 20/05/2012 then i want record of 16,17,18,19 and 20/05/2012)
Look at the examples posted here. It will solve your doubts
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
here is a more esoteric way to convert a datetime to a date only: I also think it uses the least sql resources:
declare @MyDate datetime
select @MyDate = GETDATE()
select convert(datetime,floor(convert(float, @MyDate)))
very good article
Hello sir.. Above are really good information .But i have single doubt about datediff() funtion. select DATEDIFF (wk,0,getdate()) . it returns some integer value . how it works .please tell me sir
I’m needing to retrieve a count on a column from 8:45 PM the previous night to 9:45 PM of the current day. How can I accomplish this?
Hi.. i am not that much experienced on SQl but will explain you how the DATEDIFF function works.
We must pass 3 Arguments i.e., datepart, Source date and Destintion date.The source and destination dates are nothing but, from which date to which date we want to see the dateDiff.
For Example1:SELECT DATEDIFF(yy, ’07/12/1987′, GETDATE())–OUTPUT: 25
Will returns the No:of years from ’07/12/2012′ and to current year 2012.
Example2: SELECT DATEDIFF(MM, ’07/12/2012′, GETDATE())–OUTPUT: 5
Will returns the No:of days from the day of ’07/12/2012′ to ’16/12/2012′.
So we must pass the Sorce and destination Dates.
I am surprised, how the DateDiff function works correctly without passing Source date.
So, can any one of you please explain?
Hi mustanglyf, can you please share the table and it’s columns that you are trying to retrieve a count.
Another Simple way to find the Last day of current month
select day(Dateadd(dd,-day(GETDATE()),(DATEADD(mm,1,GETDATE()))))
select day(Dateadd(dd,-day(’11/07/1987′),(DATEADD(mm,1,’11/07/1987′))))–OUTPUT-30
select day(Dateadd(dd,-day(’12/07/1987′),(DATEADD(mm,1,’12/07/1987′))))–OUTPUT-31
Not sure if this has happened to anyone else, but this web page is displaying the queries without the 0′s. If you copy/paste, you’ll get a syntax error. Add 0′s after the stray commas to correct it.
Great post, by the way. I use it almost every week.
hey hai, i have a query on Sql server 2005 using Adventure Works DB
1)write a query to display the order date along with the sales order ID and territory name.The Order date should displayed in the dd/mm/yyyy format.
2)write a query to display the orderID and territory name of the order where the month of order is may and the year is 2004.
In How many forms we can write this 2 queries plz mention the numerics before the answer..
Hi,
How do we get the Start date of the Quarter for the Fiscal Year (April-March, Jun-May) dynamicaly
Thanks
SELECT
dateadd(quarter,datediff(quarter,0,getdate()),0) as first_day_of_quarter,
dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as last_day_of_quarter
Refer this for more information (Last section of blog post)
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
HI Madhivanan,
Thanks a lot, very usefull. Have a nice day
How can I find the Quarter from the 1st day of the week? For example 12/31/2012 is the firstday of the week (Monday) , I want to derive that it is Q1 2013. How can I do that?
Hi madhivanan,
thanks for your help.i need same lyk not day i need previous year and previous month.can any one suggest for thtt.
how to get any format specific date from any table
fro example if want to get name and time of joining date of persons in any employee table in order of date-month-year hh:mm
Hi,
I am trying to calculate the average time between a series of dates (actfinish) on work orders (wonum) in a maintenance system (Maximo) that match a specific PM Number (pmnum) with T-SQL. I ran across this code but am having a difficult time understanding it enough to work in a “select – where” clause of my system. Can you suggest anything?
Code working with:
;WITH base AS
(
SELECT CustomerID,
ROW_NUMBER() over (partition BY CustomerID ORDER BY CreationDate, OrderID) AS rn
FROM [Order]
)
SELECT b1.CustomerID,
AVG(DATEDIFF(DAY,b1.CreationDate, b2.CreationDate) )
FROM base b1
JOIN base b2
ON b1.CustomerID=b2.CustomerID
AND b2.rn =b1.rn+1
GROUP BY b1.CustomerID
MikeN
Hi,
try this:
SELECT DATEADD(wk,DATEDIFF(wk,0,’2013-02-03′),0)
it will give you a wrong result, how is the 1sr day of the week is greater than the selected date!!!
Hello,
I need to get the day “Monday” and not the date.
For example I used:
SELECT GETDATE(); this will give me the date, but how can I convert the date to give me the day (Monday) instead? What sql querry can I use?
I’m new at this (student).
Any help is greatly appreciated.
Here is your answer
select datename(dw,getdate()) –Friday
Thank you Jeff.