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
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
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
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