help with datediff and datepart in sql express

Question

help with datediff and datepart in sql express

webconcepts

SSC Rookie

Points: 48
More actions
October 24, 2008 at 5:01 pm

#192531

I am tring to figure out how to use datediff and datepart in a syntax that will count only the records in a table from a specific month and return that count.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Can you please post some sample data and your desired output.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

webconcepts SSC Rookie Points: 48 More actions · Answer 2

Here is some data that I used to insert into a table RentalHistory:

INSERT INTO RentalHistory VALUES (462, '09-22-2008', '09-24-2008', 317, 111, NULL );

INSERT INTO RentalHistory VALUES (463, '09-22-2008', '09-25-2008', 329, 112, NULL );

INSERT INTO RentalHistory VALUES (464, '09-23-2008', '09-26-2008', 310, 112, NULL );

INSERT INTO RentalHistory VALUES (465, '09-24-2008', '09-27-2008', 311, 113, NULL );

INSERT INTO RentalHistory VALUES (466, '09-24-2008', '10-01-2008', 312, 113, NULL );

INSERT INTO RentalHistory VALUES (467, '06-25-2008', '06-27-2008', 313, 113, NULL );

INSERT INTO RentalHistory VALUES (468, '06-25-2008', '06-27-2008', 314, 114, NULL );

INSERT INTO RentalHistory VALUES (469, '06-25-2008', '06-27-2008', 315, 114, NULL );

INSERT INTO RentalHistory VALUES (470, '06-25-2008', '06-27-2008', 316, 115, NULL );

INSERT INTO RentalHistory VALUES (471, '06-25-2008', '06-27-2008', 317, 115, NULL );

INSERT INTO RentalHistory VALUES (472, '06-25-2008', '06-27-2008', 325, 116, NULL );

INSERT INTO RentalHistory VALUES (473, '07-27-2008', '07-29-2008', 319, 116, NULL );

INSERT INTO RentalHistory VALUES (474, '07-27-2008', '07-29-2008', 320, 113, NULL );

INSERT INTO RentalHistory VALUES (475, '07-27-2008', '07-29-2008', 321, 110, NULL );

The result that I am looking for is something like this (were all of 09/2008 is counted):

TotalRentals

------------

5

What I am looking to do is just with DATEDIFF use 'mm' for DATEPART.

webconcepts SSC Rookie Points: 48 More actions · Answer 3

I think I got what I was looking for with a little more messing around with what I already had. Seems to work like I wanted it to. This is the syntax I had come up with that seems to work.

SELECT COUNT(*) AS TotalRentals FROM RentalHistory

WHERE DATEDIFF(mm, TransactionDate, GETDATE()) = 1

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Try this...

SELECT COUNT(*)

FROM dbo.RentalHistory

WHERE FromDate >= DATEADD(mm,DATEDIFF(mm,0,'20080901'),0)

AND ToDate < DATEADD(mm,DATEDIFF(mm,0,'20080901')+1,0)

... and there's only 4 items in Sep 2008... 😉

For future reference, take a peek at the link I have in my signature to get better answers quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webconcepts SSC Rookie Points: 48 More actions · Answer 5

Thanks, by the way count again LOL first 5 inserts are for the month of 09.......:D

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

webconcepts (10/25/2008)
Thanks, by the way count again LOL first 5 inserts are for the month of 09.......:D

My fault... didn't copy the first line.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)