get qtr,month and year from start date

Question

get qtr,month and year from start date

Papil

SSCommitted

Points: 1983
More actions
November 5, 2018 at 5:05 pm

#409286

I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable

Hope this gets you started:
declare @ThisDate datetime = '20180201';

with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]

Papil SSCommitted Points: 1983 More actions · Answer 2

Lynn Pettis - Monday, November 5, 2018 6:40 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Hope this gets you started:
declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]

is it possible to get without variable. i have a column in my table and i want to add rest of these columns to get all data in one temp table?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Papil - Monday, November 5, 2018 7:06 PM
Lynn Pettis - Monday, November 5, 2018 6:40 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Hope this gets you started:
declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]
is it possible to get without variable. i have a column in my table and i want to add rest of these columns to get all data in one temp table?

The code I posted is just a sample of how to do it.

Papil SSCommitted Points: 1983 More actions · Answer 4

Lynn Pettis - Monday, November 5, 2018 7:10 PM
Papil - Monday, November 5, 2018 7:06 PM
Lynn Pettis - Monday, November 5, 2018 6:40 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Hope this gets you started:
declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]
is it possible to get without variable. i have a column in my table and i want to add rest of these columns to get all data in one temp table?
The code I posted is just a sample of how to do it.

Even if i put select statement.it only displays those rows that were showing up before.

declare @ThisDate datetime
SELECT @thisdate = ET.Thisdate
FROM mytable ET
;

--declare @ThisDate datetime = '20180201';

with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n))
select
dateadd(month,n,@ThisDate)
, dateadd(month,11,dateadd(month,n,@ThisDate))
, datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear]
, datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter]
, datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth]
from
[TwelveMonths]

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable

Papil - Monday, November 5, 2018 7:39 PM
Lynn Pettis - Monday, November 5, 2018 7:10 PM
Papil - Monday, November 5, 2018 7:06 PM
Lynn Pettis - Monday, November 5, 2018 6:40 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Hope this gets you started:
declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]
is it possible to get without variable. i have a column in my table and i want to add rest of these columns to get all data in one temp table?
The code I posted is just a sample of how to do it.
Even if i put select statement.it only displays those rows that were showing up before.
declare @ThisDate datetime
SELECT @thisdate = ET.Thisdate
FROM mytable ET
;
--declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n))
select
dateadd(month,n,@ThisDate)
, dateadd(month,11,dateadd(month,n,@ThisDate))
, datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear]
, datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter]
, datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth]
from
[TwelveMonths]

Yes... please post some sample data from your table in a readily consumable format so that one of us can demonstrate. Please see the first link in my signature line below for one way how to do that. You'll also find that most people won't even look at attached spreadsheets.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Papil SSCommitted Points: 1983 More actions · Answer 6

Jeff Moden - Monday, November 5, 2018 8:00 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Papil - Monday, November 5, 2018 7:39 PM
Lynn Pettis - Monday, November 5, 2018 7:10 PM
Papil - Monday, November 5, 2018 7:06 PM
Lynn Pettis - Monday, November 5, 2018 6:40 PM
Papil - Monday, November 5, 2018 5:05 PM
I have start date column. how can i get rest of 4 columns of attached file. financial year begins from feb 2018. so feb,mar,april-quarter 1 2018 but belongs to fiscal year 2019.
SELECT startdate, CASE WHEN MONTH(startdate) BETWEEN 2 AND 4 THEN convert(char(4), YEAR(startdate) - 1) + 'Q1' WHEN MONTH(startdate) BETWEEN 5 AND 7 THEN convert(char(4), YEAR(startdate) - 1) + 'Q2' WHEN MONTH(startdate) BETWEEN 8 AND 10 THEN convert(char(4), YEAR(startdate) - 0) + 'Q3' WHEN MONTH(startdate) BETWEEN 11 AND 1 THEN convert(char(4), YEAR(startdate) - 0) + 'Q4' END AS Quarter FROM mytable
Hope this gets you started:
declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n)) select dateadd(month,n,@ThisDate) , dateadd(month,11,dateadd(month,n,@ThisDate)) , datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear] , datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter] , datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth] from [TwelveMonths]
is it possible to get without variable. i have a column in my table and i want to add rest of these columns to get all data in one temp table?
The code I posted is just a sample of how to do it.
Even if i put select statement.it only displays those rows that were showing up before.
declare @ThisDate datetime
SELECT @thisdate = ET.Thisdate
FROM mytable ET
;
--declare @ThisDate datetime = '20180201';
with TwelveMonths as (select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n))
select
dateadd(month,n,@ThisDate)
, dateadd(month,11,dateadd(month,n,@ThisDate))
, datepart(year,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalYear]
, datepart(quarter,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalQuarter]
, datepart(month,dateadd(month,11,dateadd(month,n,@ThisDate))) as [FiscalMonth]
from
[TwelveMonths]
Yes... please post some sample data from your table in a readily consumable format so that one of us can demonstrate. Please see the first link in my signature line below for one way how to do that. You'll also find that most people won't even look at attached spreadsheets.

CREATE TABLE mytable( StartDate DATE NOT NULL PRIMARY KEY ,month INTEGER NOT NULL ,year INTEGER NOT NULL ,fy_year INTEGER NOT NULL ,fy_qtr INTEGER NOT NULL ); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-02-01',2,2018,2019,1); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-03-01',3,2018,2019,1); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-04-01',4,2018,2019,1); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-05-01',5,2018,2019,2); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-06-01',6,2018,2019,2); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-07-01',7,2018,2019,2); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-08-01',8,2018,2019,3); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-09-01',9,2018,2019,3); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-10-01',10,2018,2019,3); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-11-01',11,2018,2019,4); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2018-12-01',12,2018,2019,4); INSERT INTO mytable(StartDate,month,year,fy_year,fy_qtr) VALUES ('2019-01-01',1,2019,2019,4);

Papil SSCommitted Points: 1983 More actions · Answer 7

I have start date but want to get rest of columns in my table. I have attached consumable script above.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Okay, here you go.
create table [dbo].[mytable] ( [StartDate] date not null primary key , [CalendarMonth] as datepart(month,[StartDate]) persisted , [CalendarYear] as datepart(year,[StartDate]) persisted , [fy_year] as datepart(year,dateadd(month,11,[StartDate])) persisted , [fy_qtr] as datepart(quarter,dateadd(month,11,[StartDate])) persisted , [fy_month] as datepart(month,dateadd(month,11,[StartDate])) persisted ); go INSERT INTO mytable(StartDate) values ('2018-02-01'), ('2018-03-01'), ('2018-04-01'), ('2018-05-01'), ('2018-06-01'), ('2018-07-01'), ('2018-08-01'), ('2018-09-01'), ('2018-10-01'), ('2018-11-01'), ('2018-12-01'), ('2019-01-01'); go select * from [dbo].[mytable]; go drop table [dbo].[mytable]; -- clean up my SANDBOX DATABASE!!!!! go

drew.allen SSC Guru Points: 76944 More actions · Answer 9

Using Lynn's code and replacing the code for the sample date with your actual table field.

SELECT * , datepart(year,dateadd(month,11,StartDate)) as [FiscalYear] , datepart(quarter,dateadd(month,11,StartDate)) as [FiscalQuarter] , datepart(month,dateadd(month,11,StartDate)) as [FiscalMonth]

FROM mytable

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

drew.allen - Tuesday, November 6, 2018 9:38 AM
Using Lynn's code and replacing the code for the sample date with your actual table field.
SELECT * , datepart(year,dateadd(month,11,StartDate)) as [FiscalYear] , datepart(quarter,dateadd(month,11,StartDate)) as [FiscalQuarter] , datepart(month,dateadd(month,11,StartDate)) as [FiscalMonth]
FROM mytable
Drew

I just persisted the data in the table when inserting StartDates. Makes it easier.

Papil SSCommitted Points: 1983 More actions · Answer 11

Papil

SSCommitted

Points: 1983

November 6, 2018 at 9:57 am

#2012571

Thanks both .it works.