Get data order by date and date should be start from current date in sql.

Question

1.00/5 (1 vote)

See more:

get data order by date and date should be start from current date in sql

I have some record like 

Thought		Date
A			01 Jan
B			02 Jan
C			03 Jan
….			…..

X			31 Dec

Now I Want To Output like – Current Date(18 Sep 2016)

Thought		Date
P			18 Sep
Q			19 Sep
R			20 Sep
….			……..
X			31 Dec
A 			01 Jan
B			01 Jan
…..			………
Y			01 Sep
…..			……
Z			17 Sep

What I have tried:

SELECT ThoughtId, CompanyCode, CompanyId, Thought,
 convert(varchar(6),ThoughtDate,106) ThoughtDate 
 ,(case when isnull(IsActive,0)=0 then 'InActive' else 'Active' end)as Status,
  CreatedBy, CreatedDate, ModifyBy, ModifyDate      
FROM  ThoughtofTheDay
WHERE  CompanyId=214      
ORDER BY ABS(DATEDIFF(day, ThoughtDate, GETDATE()))asc

Thought ThoughtDate1 Status
A 18-Sep Active
CA 17-Sep Active
B 19-Sep Active
B 20-Sep Active
p 16-Sep Active
i 15-Sep Active
f 21-Sep Active
j 22-Sep Active

In above output I want that date which has passed will come in future date
like 17 Sep,16 Sep,15 sep should come in 2017(16 sep 2017....)

Posted 17-Sep-16 20:15pm

suneel kumar gupta

Updated 18-Sep-16 9:19am

v6

Add a Solution

Comments

Maciej Los 18-Sep-16 2:42am

And what's wrong with your query?

suneel kumar gupta 18-Sep-16 5:08am

Please check question I have explained my requirements with some examples.

Karthik_Mahalingam 18-Sep-16 13:38pm

Always use Reply button to post comments/query to the concerned user, so that the user gets notified and respond to your text.

2 solutions

Solution 1

If I understand your question correctly, you need to add a condition to fetch only thoughts starting from current date and change the sorting. Something like

SELECT ThoughtId, 
       CompanyCode, 
       CompanyId, 
       Thought,
       convert(varchar(6),ThoughtDate,106) ThoughtDate,
       case 
          when isnull(IsActive,0)=0 then 'InActive' 
          else 'Active' 
       end as Status,
       CreatedBy, 
       CreatedDate, 
       ModifyBy, 
       ModifyDate      
FROM  ThoughtofTheDay
WHERE CompanyId=214      
AND   ThoughtDate >= GETDATE()
ORDER BY ThoughDate ASC

Posted 17-Sep-16 21:32pm

Wendelius

Updated 18-Sep-16 1:31am

v2

Comments

Karthik_Mahalingam 18-Sep-16 4:54am

Mika
OP has mentioned "date should be start from current date" so your code has to be changed as ThoughtDate >= GETDATE()
correct me if i am wrong.

suneel kumar gupta 18-Sep-16 5:10am

Please check question I have explained my requirements with some examples.

Wendelius 18-Sep-16 7:32am

Okay, if you need dates from this date forward, simply change the operator from less than to greater than. I modified the example.

Wendelius 18-Sep-16 7:33am

Good point, than you. I misunderstood the requirement.

Karthik_Mahalingam 18-Sep-16 7:58am

:-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-09-18T09:19:00

If i understand you correctly...

You need to modify ORDER BY clause this way:

SQL

ORDER BY
    CASE WHEN ThoughtDate >= @passeddate AND ThoughtDate < DAYADD(DD, 3, @passeddate) THEN ThoughtDate END ASC,
    CASE WHEN ThoughtDate < @passeddate THEN ThoughtDate END DESC

For further details, please see:
SQL Server – Custom sorting in ORDER BY clause | Sql And Me[^]
SQL SERVER - CASE Statement in ORDER BY Clause - ORDER BY using Variable - Journey to SQL Authority with Pinal Dave[^]
ORDER BY CASE | SQL with Manoj[^]