how to get from date to todate wise get current month record

Question

0.00/5 (No votes)

See more:

How to get current month details in from date to todate wise in sql

My query:

SQL

SELECT  a.Staff_Id AS StaffID ,
        a.From_Date ,
        UPPER(b.Staff_Name) AS StaffName ,
        a.From_Date ,
        a.To_Date ,
        a.Total_Days AS TotalDays ,
        CONVERT(VARCHAR(24), Applied_Date, 113) AS Applied ,
        a.Reason AS Reason ,
        Status = ( CASE a.Status
                     WHEN 'I' THEN 'Applied'
                     WHEN 'A' THEN 'Approved'
                     WHEN 'R' THEN 'Rejected'
                     WHEN 'C' THEN 'Cancelled'
                     ELSE a.status
                   END ) ,
        Done_By = ( SELECT  UPPER(staff_name)
                    FROM    Accounts_Emp_Master emp
                    WHERE   emp.staff_id = Done_by
                  ) ,
        CONVERT(VARCHAR(11), Done_Date, 106) AS DoneDate
FROM    Accounts_LeaveEntry_New a ,
        Accounts_Emp_Master b
WHERE   a.Staff_Id = b.Staff_Id
        AND ( ( '2014-12-01' <= a.From_Date
                AND '2014-12-01' >= a.To_Date
              )
              OR ( '2014-12-01' >= a.From_Date
                   AND '2014-12-01' <= a.To_Date
                 )
            )
        AND ( b.Department = 3 )
ORDER BY a.From_Date ASC

above query wise

First Check:

SQL

(('2014-12-01' <= a.From_Date And '2014-12-01' >= a.To_Date) or ('2014-12-01' >=a.From_Date and '2014-12-01' <= a.To_Date))

this date wise above query check Last month record came
ex:

StaffID	StaffName	   Fromdate	ToDate	    TotalDays	Applied
1059	T T SIVAM	   29-Nov-14	7-Dec-14	7	24-Nov-14
1029	ROY CHOUDURY P K   1-Dec-14	31-Jan-15	54	13-Nov-14

I want result is (december)month first date record only

StaffID	StaffName	   Fromdate	ToDate	    TotalDays	Applied
1029	ROY CHOUDURY P K   1-Dec-14	31-Jan-15	54	13-Nov-14

Posted 18-Dec-14 0:25am

Nirmal Chandru

Updated 18-Dec-14 0:58am

Tomas Takac

v3

Add a Solution

2 solutions

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)

OriginalGriff · Accepted Answer · 2014-12-18T01:09:00

Solution 1

Um.
10 Nov 2014 fails the '2014-12-01' <= a.From_Date but passes the '2014-12-01' >= a.From_Date
10 Dec 2014 passes the '2014-12-01' <= a.From_Date but fails the '2014-12-01' >= a.From_Date
10 Jan 2014 passes the '2014-12-01' <= a.From_Date but fails the '2014-12-01' >= a.From_Date
So any data at all will pass some part of your condition, it depends on the "to date" whether the whole will pass.

Isn't what you want really just :

SQL

'2014-12-01' BETWEEN a.From_Date AND a.To_Date

Posted 18-Dec-14 1:09am

OriginalGriff

Comments

Nirmal Chandru 18-Dec-14 7:19am

Hai sir,

Your send query wise check but Previous month date also came below that query me given.
query already given

StaffID StaffName Fromdate ToDate TotalDays Applied
1059 T T SIVAM 29-Nov-14 7-Dec-14 7 24-Nov-14
1029 ROY CHOUDURY P K 1-Dec-14 31-Jan-15 54 13-Nov-14

Result first record is last month record came so i don't want display that record.
this is my problem.
Thanks

Shweta N Mishra · Accepted Answer · 2014-12-18T01:14:00

Solution 2

I think your where condition should be

SQL

Where a.From_date>='2014-12-01' and a.To_date>a.From_date and a.To_Date>='2014-12-01'

Posted 18-Dec-14 1:14am

Shweta N Mishra

Comments

Nirmal Chandru 18-Dec-14 7:45am

above code wise i am worked but not came particularly 2014-12-01 this date result but came that month all data came.

I want particularly 2014-12-01 this date record only

Thanks

Nirmal Chandru 18-Dec-14 9:12am

Thank you