Wednesday, July 16, 2014

How to find Quarter Name and Month Name from the Datetime in MS SQL

Recently I was developing one Material Management System and got a requirement to get the details quarterly with the Month name in a report.
Since it is a simple query I thought I will share you he same as it may might be useful.
Lets create the table first.
create table Items
(
ItemName varchar(500),
uom char(200),
Quantity float(50),
Price numeric(5,2),
Bill_Date datetime
)

Now we will insert the sample data,
insert into Items values ('Sugar','kgs','5','300','2014-04-05')
insert into Items values ('Rice','kgs','10','600','2014-06-15')
insert into Items values ('Dhaal','kgs','8','650','2014-08-05')
insert into Items values ('Oil','Ltr','5','400','2014-11-05')

We have both Table and data available now, so lets query the table to get quarter name and month name from the inserted datetime data.
SELECT 
ItemName, 
UOM, 
Quantity,
DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), Bill_Date) AS DATETIME)) as [quarter],
DATENAME(MONTH, Bill_Date) as [Month] from Items

Lets look at the output now,

image
In the above screen print you can see that the quarter shown as 2, 3 and 4.
The query we have used here is very simple,
select DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), Bill_Date) AS DATETIME)) as [quarter] from Items
I hope this may help you one day when you have to query quarter name.
 

2 comments:

Cub Training said...

It was so nice article and useful to Informatica learners. we also provide Dotnet Course online training our Cubtraining is leader in providing Software Training

Cub Training said...

It was so nice article and useful to Informatica learners. we also provide Dotnet Course online training our Cubtraining is leader in providing Software Training