SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

« SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

SQL SERVER – FIX : ERROR : Could Not Connect to SQL Server – TDSSNIClient initialization failed with error 0x7e, status code 0×60 »

SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

August 29, 2008 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 69 Comments

69 Responses

on August 30, 2008 at 3:36 am | Reply Marek Śliwiński

thx! @ll in 1 place :) Great! fav+
on September 2, 2008 at 6:24 pm | Reply Jay

–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))
on September 11, 2008 at 5:30 pm | Reply Sathish

Hi,
The above informations are really very fantastic..
May i know the how to down load sqlserver 2008 book.
on September 12, 2008 at 7:27 pm | Reply Druez

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?
- on November 11, 2010 at 2:07 pm | Reply madhivanan
  
  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
on September 16, 2008 at 3:31 pm | Reply Rama Krishna.K.S.

This collection will be useful for everybody, wonderful
on September 23, 2008 at 3:27 pm | Reply saravanan

Hey.., information was sufficient enough…, thanks and good bye
on September 24, 2008 at 9:26 pm | Reply Chris Bui

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
on October 1, 2008 at 10:42 pm | Reply m

what if you want results from a specific date? for example, the 3rd of every month?
- on November 11, 2010 at 2:04 pm | Reply madhivanan
  
  select dateadd(month,datediff(month,0,getdate()),0)+2
on October 6, 2008 at 9:24 pm | Reply Kiran

Great Information .Thank You
on October 15, 2008 at 3:53 pm | Reply Brahma Reddy

it s most usefull for developer
on February 3, 2009 at 3:53 am | Reply David Stachon

Thanks so much! A real life saver….well, at the very least, a huge time and mental anguish saver.
on February 17, 2009 at 4:35 pm | Reply Pedro

Very useful functions no doubt , real life saver Thanks.
on April 2, 2009 at 8:46 am | Reply Tom

Very useful indeed. Thanks.
on April 6, 2009 at 4:34 am | Reply RSJ

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
on April 17, 2009 at 6:51 pm | Reply Rajnish

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 .
on April 18, 2009 at 3:02 pm | Reply Tejas Shah

Hi Rajnish,

You can use:

DATEDIFF(mm, Date1, Date2) to get month difference between Dates.

Thanks,

Tejas
on June 17, 2009 at 2:46 pm | Reply Cat

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?
- on November 11, 2010 at 2:02 pm | Reply madhivanan
  
  select dateadd(month,-3,getdate())
on June 17, 2009 at 8:55 pm | Reply Cat

It’s ok I’ve found out now for myself. Gotta love S Q L!
on July 14, 2009 at 12:03 pm | Reply Samba

Hi Dave,
Thanq very much for the above information.I appreciate your efforts
on August 5, 2009 at 5:16 pm | Reply raj

Great Tips,
on August 23, 2009 at 10:43 pm | Reply Mysoor TechTeam

Great Article and BEST SITE!!! You are hero!
on October 2, 2009 at 3:12 am | Reply Victor

Hi, I always read your posts, but this one was the best.

Thanks
on October 14, 2009 at 3:58 pm | Reply Shahaji Udar

Hi,

Its really very helpful information.
Thanks a lot.

It saves lot of development&/RD work.

Thanks Again !!!

Shahaji Udar

Pune(India)
on February 18, 2010 at 10:20 pm | Reply John Lambert

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.
- on March 10, 2010 at 12:59 am | Reply Sudhir
  
  Sql Server 2005
  
  Today = select convert(nvarchar(12), getdate(), 101)
  
  Sudhir
on February 24, 2010 at 5:45 am | Reply Jeff

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
on June 3, 2010 at 3:05 pm | Reply Sanjeev Puri

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.
- on July 5, 2011 at 8:05 pm | Reply madhivanan
  
  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
on June 3, 2010 at 3:08 pm | Reply Sanjeev Puri

I want to calculate DayOfQuarter for the given date. Any idea, how we can do it?

Thanks,
Sanjeev Puri
- on October 15, 2010 at 5:06 pm | Reply bhumi Dev
  
  –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))
on October 2, 2010 at 12:55 am | Reply Maggie

Awesome – exactly what I was looking for! Thanks
on October 4, 2010 at 11:03 pm | Reply Jeff Muzzy

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

*/
on November 8, 2010 at 9:16 am | Reply Raj

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.
- on November 8, 2010 at 1:50 pm | Reply madhivanan
  
  Post table structure, sample data and expected result
  - on November 10, 2010 at 4:42 am | Reply Raj
    
    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.
    - on November 11, 2010 at 2:00 pm | Reply madhivanan
      
      where datecol>=dateadd(month,-1,@monthyear) and datecol<dateadd(month,0,@monthyear)
on November 11, 2010 at 10:36 pm | Reply raj.

Hi Madhivanan,

Thanks for your reply, hope it solves my issue.

Thanks,
Raj.
on November 14, 2010 at 10:23 pm | Reply Prawin

Thanks a lot for this article. Was very helpful in writing some specific dates.
on January 4, 2011 at 7:00 am | Reply Sue

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.
- on January 5, 2011 at 7:14 pm | Reply madhivanan
  
  You can use a number table and increment the date value till it ends with the last date
on January 11, 2011 at 1:29 pm | Reply Siju

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…
- on January 11, 2011 at 4:36 pm | Reply madhivanan
  
  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
  - on January 12, 2011 at 12:33 pm | Reply Siju
    
    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…
on January 12, 2011 at 11:20 am | Reply Siju

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…
- on January 14, 2011 at 5:42 pm | Reply madhivanan
  
  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
on January 18, 2011 at 12:00 pm | Reply Eden

Really helpful date formats….all in one
Thanks
on March 12, 2011 at 2:44 pm | Reply Shibu P

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
on March 18, 2011 at 5:35 am | Reply Khanh

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!
- on June 1, 2011 at 2:21 pm | Reply madhivanan
  
  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
on May 26, 2011 at 12:18 am | Reply Brian

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
- on June 1, 2011 at 2:20 pm | Reply madhivanan
  
  Please refer this post for more informations
  http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx
on June 1, 2011 at 2:20 pm | Reply madhivanan

The following will also help you
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx
on July 26, 2011 at 4:56 pm | Reply Sure

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?
on July 27, 2011 at 3:58 am | Reply Jeff Muzzy

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;
on July 27, 2011 at 11:33 pm | Reply Jeff Muzzy

—-Today
SELECT GetUTCDate() ‘UTC Time’
on August 5, 2011 at 2:31 am | Reply C.Willy

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.
on September 15, 2011 at 9:35 pm | Reply George

This is just perfect my friend. Really helpfull. Thanks, George from Buenos Aires.
on November 8, 2011 at 2:17 am | Reply Vincent

great article, very helpful thank you.
on November 13, 2011 at 8:13 am | Reply ak

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)
on November 13, 2011 at 8:19 am | Reply ak

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
on November 15, 2011 at 7:47 pm | Reply Swapan

awesome. helped to recapitulate. understand the logic and it is as easy as abc.
on December 20, 2011 at 11:23 am | Reply LA

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?
- on December 20, 2011 at 3:02 pm | Reply madhivanan
  
  Start with this
  
  select dateadd(day,number,’20001010′) as dates from master..spt_values
  where type=’p’ order by number
on December 28, 2011 at 11:34 am | Reply srinath

Hi, Any body can tell me that how to find last sunday date of the current month and next month.
on January 11, 2012 at 6:20 pm | Reply Wilma

Thank you very much for this post, it saved me a lot of time!
on February 7, 2012 at 1:19 am | Reply RenaT

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