SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 convert quarter to date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

175 Posts

Posted - 02/19/2014 :  14:41:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/19/2014 :  15:41:55  Show Profile  Reply with Quote
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
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 02/20/2014 :  08:41:33  Show Profile  Reply with Quote

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

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/20/2014 :  11:26:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000