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 (https://blog.sqlauthority.com), Vivek Jamwal
112 Comments. Leave new
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)
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.
Hi, I have query like:
Our Quarter Starts in June, it says as Week 1
The User Is Passing the value: Test_1304_wk5
it means:
Test: Some revision name,
13 : Year,
04 : Quarter,
WK 5 : Week 5 in fourth quarter.
Using this info i have get like: 201347 ( Total Week Id)
2013 : Year
47 : week id according to our Quarter.
Presently we are in fourth quarter, and week 47 out of 52 weeks
Is it Possible, please help me..
The formulas for First Day of Last Week and Last Day of Last Week appear to bring back a week starting on Monday and ending on Sunday.
How would you modify it to bring back a week starting on Sunday and ending on Saturday?
And what is the significants of using 7 in the formulas?
dateadd(wk,datediff(wk,7,getdate()),0)
dateadd(wk,datediff(wk,7,getdate()),6)
Hi,
How I can find the right date of the same day but in the last year?
For example today is 2014-10-01 Wednesday, I need a function to find the same Wednesday but in the last year, in this case the function results should be 2013-10-02.
Thanks!
Hi
How do I set a computed field value for a date field from two other date fields
or one date field and one int field.
permissiondate date
prodnleadtime int –
prodnstart date – compute value as permissiondate + prodnleadtime
How to do this?
How to subtract two dates and set this as a date for another date field in the table?
Can some one point me to right steps on calculation of dates in sqlserver (ssms)?
I would like my end date to be 4 weeks prior to todays date?
Team,
I can not figure out why this work. I need to know for myself; please!
DATEADD(dd, 6 – (@@DATEFIRST + 5 + DATEPART(dw, [DateFiledInTable])) % 7, [Date]) as week_ending_date_week_starts_on_monday
Thanks in advance
Very helpfull. Cool.