returning all dates that match year and month

Question

returning all dates that match year and month

jerome.morris

SSCrazy

Points: 2829
More actions
November 15, 2012 at 8:09 am

#284953

SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, EndDate)) AND (DATEPART(mm, @date1) = DATEPART(mm, EndDate))
What am I doing wrong I only want to return the year and month that match
10/10/2012 so all end dates in oct for the year 2012
Without converting! the EndDate is datetime column
Thanks
J

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

jerome.morris (11/15/2012)
SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, EndDate)) AND (DATEPART(mm, @date1) = DATEPART(mm, EndDate))
What am I doing wrong I only want to return the year and month that match
10/10/2012 so all end dates in oct for the year 2012
Without converting! the EndDate is datetime column
Thanks
J

I don't see that you are doing anything wrong at least logically. You have however put together a nonSARGable where clause. How about this instead?

Where EndDate > dateadd(mm, datediff(mm, 0, @date1), 0)

and EndDate < dateadd(mm, datediff(mm, 0, @date1) + 1, 0)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

Hi Sean, am I being dumb? I cant see how it matches the year ?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

jerome.morris (11/15/2012)
Hi Sean, am I being dumb? I cant see how it matches the year ?

Since it only selects data greater than the start of the month and before the start of the next month, it has to be in the same year.

You should try testing with it to see if it works for you.

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

Hi Micheal, I did test it and it worked but wanted to understand how it was working

Thanks

J

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

jerome.morris (11/15/2012)
Hi Micheal, I did test it and it worked but wanted to understand how it was working
Thanks
J

Jerome,

I borrowed heavily from the article Lynn Pettis wrote on dates. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url] Read the comments in that article. It will explain the logic. Let me know if you need any further explanation.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

math martinez SSC Veteran Points: 285 More actions · Answer 6

Hi Jerome!

You don't need to use just to match the year and the month of your parameter. Try this query

SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,

MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,

TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,

PiecesCompleted, Id

FROM ProdDataTB

WHERE (YEAR(EndDate) = YEAR(@date1)) AND (MONTH(EndDate) = MONTH(@date1))

Hope i can help:-)

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

math martinez (11/19/2012)
Hi Jerome!
You don't need to use just to match the year and the month of your parameter. Try this query
SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (YEAR(EndDate) = YEAR(@date1)) AND (MONTH(EndDate) = MONTH(@date1))
Hope i can help:-)

This is a little easier to read than the original post but has the same issues with SARGability. It will cause a full scan of the table because the functions in the where clause are referencing columns in the table. That means the optimizer has to evaluate every single row to see if it is a match.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/