datediffer in sql...

Question

0.00/5 (No votes)

See more:

SQL

if i select date from 2014-03-14 to 2014-04-06...then i want days count of perticular months

Posted 8-Apr-14 0:00am

Member 10512629

Add a Solution

Comments

Er. Puneet Goel 8-Apr-14 6:05am

you want to get diff in days or what?

Member 10512629 8-Apr-14 6:33am

i want days counts between 14 march to 30 march and 1 april to 6 april as saporate when i give input '2014-03-14' AND '2014-04-06'

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 · Answer 1 · 2014-04-08T00:04:00

Solution 1

Do you mean:

SQL

SELECT COUNT(*) FROM MyTable WHERE insertDate BETWEEN '2014-03-14' AND '2014-04-06'

If not, please try to explain in more detail - perhaps with an example?

Posted 8-Apr-14 0:04am

OriginalGriff

Comments

Maciej Los 8-Apr-14 6:20am

Maybe you're right, +5!
Please, see my answer.

Member 10512629 8-Apr-14 6:27am

i want days counts between 14 march to 30 march and 1 april to 6 april as saporate when i give input '2014-03-14' AND '2014-04-06'

Maciej Los · Answer 2 · 2014-04-08T00:17:00

Solution 2

SQL

SELECT DATEDIFF(dd,'2014-03-14', '2014-04-06') AS CountOfDaysBetweenDates

More: DATEDIFF[^]

[EDIT]

SQL

SELECT CONVERT(VARCHAR(7),entry_date,121) AS PeriodOfTime, COUNT(*) as footfall, sum(amount)/(SELECT DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, max(GE.Active_Date))) * -1, DATEADD(mm, 1,max(GE.Active_Date))))) Avgamount
FROM guest_tbl
WHERE entry_date between '2014-03-14' AND '2014-04-06'
GROUP BY CONVERT(VARCHAR(7),entry_date,121)

[/EDIT]

Posted 8-Apr-14 0:17am

Maciej Los

Updated 8-Apr-14 2:29am

v2

Comments

Member 10512629 8-Apr-14 6:27am

i want days counts between 14 march to 30 march and 1 april to 6 april as saporate when i give input '2014-03-14' AND '2014-04-06'

Maciej Los 8-Apr-14 7:26am

And... The rule is the same. All you need to do is to find last day of march and first day of april. Have you tried anything?

Member 10512629 8-Apr-14 7:32am

i have 1 sql query "select count(*) as footfall,sum(amount)/(SELECT DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, max(GE.Active_Date))) * -1, DATEADD(mm, 1,max(GE.Active_Date))))) Avgamount from guest_tbl where entry_date between '2014-03-14' AND '2014-04-06'" ..this is my query...m geting wrong avgamount as it taking 30 day

Member 10512629 8-Apr-14 8:15am

so i want o/p for march and april...as seporate

Maciej Los 8-Apr-14 8:29am

See updated answer ;)

Member 10512629 8-Apr-14 9:15am

see in this m geting avg for march as amount/31..actuly this is wrong... i want amount/19 for march...and amount/6 then i will get currect averg...thanks

Maciej Los 8-Apr-14 10:03am

You're welcome ;)
Please, mark this answer as a solution (green button).