[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

sql date quarter

Posted on 2011-02-10
5
Medium Priority
?
358 Views
Last Modified: 2012-06-27
See my code below.. It's producing a record for each month in the QTR.. I only want one record per QTR.. The results set is supposed to look like

Q1 2011
Q2 2011

NOT
Q1 2011
Q1 2011
Q1 2011

and so on
(SELECT 'Q' + DATENAME(Q, @DateListedMLS) + CAST ('  ' AS VARCHAR(1)) + CAST(DATEPART(YY, @DateListedMLS) AS VARCHAR(200)))

Open in new window

0
Comment
Question by:cheryl9063
  • 2
  • 2
5 Comments
 
LVL 1

Author Comment

by:cheryl9063
ID: 34864507
I found the issue.. But I still need to know this:

This gives me the current QTR.. Based on the current date I want the next QTR.. How can I use below to get the next QTR? For example.. The below gives me 1 and I want 2.. In reality I will have a variable with a sale date like 02/11/2010.. I want to use that variable but get the QTR after that… Not QTR1 but QTR2..

select DATENAME(Q, GETDATE())
0
 
LVL 11

Expert Comment

by:jasonduan
ID: 34864858
this gives you the next quarter date:  SELECT DATEADD(Q, 1, GETDATE())
therefore, the answer is:  select DATENAME(Q, DATEADD(Q, 1, GETDATE()))
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34865531
Same result as jason but more brief

select DatePart(Q, GETDATE()) % 4 + 1
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34865578
To get the entire Q + Y..

declare @DateListedMLS datetime set @DateListedMLS = GETDATE()
select 'Q' + Right(DatePart(Q, GETDATE()) % 4 + 1,1) +
	' ' + convert(char(4),DATEADD(M,3,@DateListedMLS),102)

Open in new window

0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 34964418
Thanks!!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question