convert quarter to date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert quarter to date

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2014-02-19 : 14:41:52

Hello,

Please help me to write statement to convert quarter to date.
For example:

Quarter field displayed as : 01Q10 (01= first quarter, Q= Quarter, 10=Year 2010)

or Another field displayed as : 1018 (10 for year, 1 for first quarter and 8 represent for quarter)

Thanks,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-19 : 15:41:55

quote:
1 for first quarter and 8 represent for quarter

Huh? 1 and 8 both represent the quarter?

- what should the results be for each of these encoded values?
- Is it always based on calender quarters? ie: Q1=Jan 1, Q2=Apr 1, Q3=Jul 1, Q4=Oct 1 ?

Be One with the Optimizer
TG

ntn104
Posting Yak Master

175 Posts

Posted - 2014-02-20 : 08:41:33

I can either use one of the field to get the result. I gave two fields so you can see the available data field that could bring result.

I want to convert the field to Date format so when the user enter the range of date, it will bring the right result of those quarter. For example, a user entered "BeginDate" = 1/1/2013 and "enddate"=3/31/2013, then it will bring up the information for Quarter 1 of 2013.

Or if we can find a way that user just enter exact as field display: 01Q13 for BeginDate, and 01Q13 for Enddate...then it will bring up the information related to quarter 1 of 2013.

Hope I am not confuse you.

quote:
Originally posted by TG

quote:
1 for first quarter and 8 represent for quarter

Huh? 1 and 8 both represent the quarter?

- what should the results be for each of these encoded values?
- Is it always based on calender quarters? ie: Q1=Jan 1, Q2=Apr 1, Q3=Jul 1, Q4=Oct 1 ?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-20 : 11:26:32

Perhaps something like this:


declare @s char(5)
set @s = '01Q13'

select dateadd(year, convert(int, right(@s, 2)), startDt) as startDt
       ,dateadd(year, convert(int, right(@s, 2)), endDt) as endDt
from   (
       select '01', convert(datetime, '2000-01-01'), convert(datetime, '2000-03-31') union all
       select '02', convert(datetime, '2000-04-01'), convert(datetime, '2000-06-30') union all
       select '03', convert(datetime, '2000-07-01'), convert(datetime, '2000-09-30') union all
       select '04', convert(datetime, '2000-10-01'), convert(datetime, '2000-12-31')
       ) d ([Q], [startDt], [endDt])
where  Q = left(@s, 2)

OUTPUT:

startDt                 endDt
----------------------- -----------------------
2013-01-01 00:00:00.000 2013-03-31 00:00:00.000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources